Stairway to SQL Server Security Level 2: Authentication

Question

Stairway to SQL Server Security Level 2: Authentication

Don Kiely

SSC Eights!

Points: 959
More actions
May 19, 2014 at 4:46 am

#288932

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

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

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

hattothecone 61911 Valued Member Points: 58 More actions · Answer 1

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

Don Kiely SSC Eights! Points: 959 More actions · Answer 2

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

hattothecone 61911 Valued Member Points: 58 More actions · Answer 3

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!

christopher.gray Old Hand Points: 372 More actions · Answer 4

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!).

Tony Trus Say Hey Kid Points: 684 More actions · Answer 5

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

christopher.gray Old Hand Points: 372 More actions · Answer 6

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 7

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.

dainolib SSC Enthusiast Points: 127 More actions · Answer 8

"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

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 9

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.