Stairway to SQL Server Security Level 2: Authentication

  • Comments posted to this topic are about the item Stairway to SQL Server Security Level 2: Authentication

  • Hey fantastic article, I am just wondering though, I would just suggest that you write with more gender neutral language. Even as a woman, I feel strongly about gender neutral language; If it's wrong to use "he", then it's also wrong to use "she" by itself in such a context. Try using "he or she" or even better "they" or "their".

    Thanks!

    - Deb

  • Thanks for the kind words! Yep, going for gender-neutral language is a challenge, and I'll think more on your comments.

  • OK thanks. It is odd though; I thought that if you weren't thinking about it, you would have used "he" more often since that is almost everybody's natural tendency, and the whole reason all this gender neutral stuff even started!

    OK, have fun my friend!

  • About the tip "Never leave the default database set to the master database."...

    I get where you are coming from; accidentally executing something in master is not fun.

    BUT also know this: if your default database is not available (offline, corrupt, restoring, etc), you will not be able to log onto the server. AT ALL.

    This includes sysadmins (though the DAC can still be used).

    Master will always be there (or if it's not, neither is anything else!).

  • christopher.gray (6/18/2014)


    About the tip "Never leave the default database set to the master database."...

    I get where you are coming from; accidentally executing something in master is not fun.

    BUT also know this: if your default database is not available (offline, corrupt, restoring, etc), you will not be able to log onto the server. AT ALL.

    This includes sysadmins (though the DAC can still be used).

    Master will always be there (or if it's not, neither is anything else!).

    I am not sure who told you this but I believe I have on a few occasions logged in to the server when the default database I was assigned to was "In Recovery" or offline. When you get the dialogue box for connecting you get another tab where you can specify an alternate database to connect. There is an OPTIONS>> button at bottom where you get additional tabs to specify additional criteria such as the scenario you described.

    Best of luck

    Tony Trus

    Onepax Business Consulting

  • Ah, you are correct, Tony.

    There is an Options box that lets you get to where you can select a database.

    The selection field is a combo box; clicking it to select a database tries to connect to the server, which will also fail with the same error (default database not available).

    But you can just type in a database name (such as master) without using the dropdown part and that will work.

    I'm not sure how widely known that is; I know I didn't know it when I ran into this issue.

    Thanks for the information, Tony. I learned something today.

  • To add to the conversation that Tony and Christopher are having, I seem to recall another "gotcha" with changing the default database from master (though I still do it). It had to do with a wizard not finishing due to an error. I wanna say it's the Restore Database wizard but I've tried searching the interwebs and I'm not finding it. Nevertheless, when it happened to me, I searched the exact error code, discovered the solution in short order, changed the default DB back to master and the wizard completed...in fact, now that my fingers have said this "out loud", I recall having to change the default to something OTHER than the DB I was trying to restore...yeah, I think that was the problem. Nevertheless, changing the default DB was the 'gotcha' but it's so minor I'll stop clamoring on about it. 😛

    Nice articles by the way. Thanks for writing them, Don.

  • "You can't modify or delete the sa login". That is not exact as you cannot delete it, but you can rename it. I usually do it and I consider it a good practice for security.

    If you want to rename sa login to "samyadmin"

    USE [master]

    GO

    ALTER LOGIN sa WITH NAME = [samyadmin];

    GO

  • Very old article / discussion, but I disagree with the tip to set another database than master as default too

    • problems (or manual modifications of settings) when the database is not available (and you are already VERY stressed)
    • when you can create objects in the master database it may be better to limit the permissions (don't login with SA permission per default and don't grant your usual account dbo permissions in master)
    • better start all your scripts with a USE [myDB] statement to prevent execution in the wrong database
    • what, if you have more than one main database?

    God is real, unless declared integer.

Viewing 10 posts - 1 through 9 (of 9 total)

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