What login should a DBA use for non-emergency work

  • 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

  • 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

  • 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 🙂

  • 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)

  • 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 🙂

  • 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

  • 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?

  • 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 🙂

  • 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 🙂

  • 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 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

  • 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 🙂

  • 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)

  • 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.

  • 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.

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

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