What login should a DBA use for non-emergency work

Question

What login should a DBA use for non-emergency work

nzrdb6

SSChasing Mays

Points: 645
More actions
September 13, 2010 at 7:49 am

#387771

I'm writing up a suite of DBA standards for a bank that has never had any (yes I know :-/). I'm recommending that DBAs use sa or equivalent only when necessary. What permissions should be used for bog standard daily checks etc? JUst looking for some ideas

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

That will vary based on your environment, but the permissions should be the minimum required to still be able to get done what needs to get done.

The Redneck DBA

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 2

sa account itself should be disabled, if you need mixed mode, fine, create an sa like user, but not 'sa'.. where possible use windows authentication for connecting, and set up some groups in ad allowing access to various roles..

absolutely rule of the minimum should apply on your security, use the account with the least amount of access to get the job done

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

torpkev (9/13/2010)
sa account itself should be disabled, if you need mixed mode, fine, create an sa like user, but not 'sa'.. where possible use windows authentication for connecting, and set up some groups in ad allowing access to various roles..
absolutely rule of the minimum should apply on your security, use the account with the least amount of access to get the job done

What is the advantage to disabling the SA account?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 4

Less chance someone will try and access your system with some different named account rather than sa.

If you have the user sa active, then if I got access to your system and wanted to hack your SQL Server, thats where I'd start, because a) I know its an account you have and b) I know it had sysadmin rights.

If sa is disabled, then its not there to try and access - I have no problem with an sa-like account, there are occasions where you may need them, but if that account was called something else, I'm less likely to find it, or even if I did know it existed, know what rights it has.

Obviously, if I'm off base here, someone should correct me (I'm not being flippant here, completely serious), but that was my understanding and amongst the first steps I take when I install SQL Server is to make sure sa is disabled if I have mixed mode turned on.

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Eric M Russell SSC Guru Points: 125255 More actions · Answer 5

I use the following script to grant permission to a "power user" role; which isn't an administrator the database but a user who occasionally needs to do things like troubleshoot performance issues, run ad-hoc queries, confirm the latest version of an object has been deployed, etc.

use database_name;

-- Grant role permission to select form any table:

EXEC sp_addrolemember 'db_datareader', 'role_name';

-- Grant role permission to show execution plans from SSMS:

grant SHOWPLAN to [role_name];

-- Server level permissions require that context be changed to [master] database.

use master

-- Grant role permission to view system tables and views:

grant VIEW SERVER STATE to [role_name];

-- Grant role permission to view object schemas:

grant VIEW ANY DEFINITION to [role_name];

-- Grant role permission to view execution plans or start traces:

grant ALTER TRACE to [role_name];

-- Grant role permission to read logs:

grant exec on xp_ReadErrorLog to [role_name];

GO

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

steveb. SSC-Forever Points: 46733 More actions · Answer 6

torpkev (9/14/2010)
Less chance someone will try and access your system with some different named account rather than sa.
If you have the user sa active, then if I got access to your system and wanted to hack your SQL Server, thats where I'd start, because a) I know its an account you have and b) I know it had sysadmin rights.
If sa is disabled, then its not there to try and access - I have no problem with an sa-like account, there are occasions where you may need them, but if that account was called something else, I'm less likely to find it, or even if I did know it existed, know what rights it has.
Obviously, if I'm off base here, someone should correct me (I'm not being flippant here, completely serious), but that was my understanding and amongst the first steps I take when I install SQL Server is to make sure sa is disabled if I have mixed mode turned on.

would it not better to give your SA a very complex password, then at least you will be aware of people trying to hack your server?

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 7

sa as a honeypot is an interesting idea - but i'd hate to be the dba that set a complex password which was then hacked over the w/end, either by pure luck or just plain laziness in creating the password (most cases I've seen of strong passwords in the real world tend to be a letter/number/character representation of a real word)

I could be wrong, not something i've tried - but i don't think you can even drop sa and recreate it as a user with no access (or drop the sysadmin role from it?)

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Martin-202776 SSC Journeyman Points: 84 More actions · Answer 8

You cant drop SA but you can disable it. If you are using windows authentification (best) then I see no reason for leaving it enabled. I never do 🙂

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

If someone is guessing/brute forcing your SA password, you have bigger issues...

I leave it enabled on my servers, but run a job every day that sends me the failed logins by count/user/server so if anything funky is going on, I know about it within 24 hours at most. There's not a computer in the world that can brute force a 12+ char password in 1 day..and especially not one that can access my intranet.

WayneS SSC Guru Points: 95405 More actions · Answer 10

Derrick Smith (9/14/2010)
If someone is guessing/brute forcing your SA password, you have bigger issues...
I leave it enabled on my servers, but run a job every day that sends me the failed logins by count/user/server so if anything funky is going on, I know about it within 24 hours at most. There's not a computer in the world that can brute force a 12+ char password in 1 day..and especially not one that can access my intranet.

Derrick,

Would you care to post the code for this? It sounds like something that would be quite beneficial to many others!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 11

i completely agree that you'd have bigger issues..

my philosophy on it though is that if it doesn't need to be there, why have it there? Why even give someone the tiniest chance that it could be compromised?

I've never understood why when it asks for the password it doesn't allow you to enter a username too if it must insist on having an sa like account.

My biggest annoyance with sa - quite outside of this discussion - is those people who use sa to connect to the database because they don't know any better - then they get a dba who gets to look at it 3 years later and find it saved in clear text in a hundred different places..

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Derrick Smith SSChampion Points: 10833 More actions · Answer 12

Sure

DECLARE @LoginAudit TABLE (

ServerName nvarchar(255) DEFAULT @@servername,

Time smalldatetime,

ProcessInfo nvarchar(50),

Text nvarchar(1000)

)

INSERT INTO @LoginAudit (Time, ProcessInfo, Text)

EXEC sp_readerrorlog 0, 1, 'Login failed'

select top 20 ServerName, Time, Text from @LoginAudit

WHERE Time > getdate()-1

ORDER BY Time DESC

I set up this job to run nightly on every server and dump it all to a central monitoring DB, and then after that's done, I have it send an email to myself grouped by server name with counts, and then if there's anything higher than a few, I'll check the table manually to get the full error/login. I also have a front-end page that sorts it by server name, and username in the error message. Here is the code to pull the username out of the Text column:

substring(Text, 24, CHARINDEX('.',Text) - 25)

steveb. SSC-Forever Points: 46733 More actions · Answer 13

torpkev (9/14/2010)
My biggest annoyance with sa - quite outside of this discussion - is those people who use sa to connect to the database because they don't know any better - then they get a dba who gets to look at it 3 years later and find it saved in clear text in a hundred different places..

agree totally with this, the sa account does get abused and it is not un-common to find it lurking around in .net config files in plain text.

Derrick Smith SSChampion Points: 10833 More actions · Answer 14

steveb. (9/14/2010)
torpkev (9/14/2010)
My biggest annoyance with sa - quite outside of this discussion - is those people who use sa to connect to the database because they don't know any better - then they get a dba who gets to look at it 3 years later and find it saved in clear text in a hundred different places..
agree totally with this, the sa account does get abused and it is not un-common to find it lurking around in .net config files in plain text.

Or, my all-time favorite...linked servers using SA.

Found a dev server recently using SA to read a table from our prod server. So, as a demo, I created a db on the prod server, logged into dev with a read-only login, and dropped the db in production. It's amazing how quickly things get changed when you do that.