As both readers of this blog know by now I used to be somewhat of a basketball player and coach. Today starts the NCAA Men’s basketball tournament, which is a tournament for many of the US colleges and universities. The tournament is a single elimination format for 64 teams, conducted in eight days, spread over three weeks. You can find out more details here.
I decided that I wanted to do one of those monthly blog post series that have been done before but I couldn’t help think about how (1) I don’t like doing something exactly like everyone else and (2) the idea of doing something for 30 or more days makes my head hurt. So, I decided to do something a little different, and to run my series from now until Championship Monday on April 4th.
The focus of my series will be on system tables. Why system tables? Great question! The reason I want to focus on system tables is because many new DBAs have no idea they exist! And for the ones that have an idea, they may not know how to use them effectively. I hope this series will help to raise awareness of the system tables and to help a new DBA use the system tables effectively.
First up is the syslogins table, which you will find in the master database. Go ahead and run the following:
SELECT * FROM master.dbo.syslogins
and examine the results. Pay attention to the columns named ‘sid’, ‘password’, ‘name’, ‘loginname’, and ‘updatedate’. Why? Because those are great columns to note when troubleshooting issues. Also note that the ability to query this system table directly will be removed from a future version of SQL Server because the object itself will be removed.
What is this table for?
This table is for storing logins, plain and simple. You should find one row for each login you have defined on your instance. That includes both SQL and Windows logins (including a Windows group) that have been created.
Why should you care?
One word: disaster. When you are in a DR scenario the last thing you want to do is go around asking people if they need a login to the server. Ideally you would be able to restore the master database in case of a DR event, but let’s say you can’t, and you have to rebuild a box from scratch. Having the logins handy makes it easier for you to begin to put things back the way they used to be.
In addition to a DR scenario, I liked capturing the login details nightly in order to facilitate restores with an automated resynch process. When restoring a database to the same server no resynch of logins and users is necessary. But when restoring between servers (say, from production to test), there is a chance that the logins on each server will not be identical. If that is the case then after the restore is completed you will need to put permissions back in place, and having the logins handy helps to automate that process.
Lastly it just helps when troubleshooting issues. When a user says “everything was FINE yesterday”, it is nice to be able to see what things looked like yesterday. I lost track a long time ago the number of times a developer would tell me that there was an issue with permissions on our end. A little digging and I would be able to re-point their ire in a different direction (usually back at themselves, but not always).
Where else is this information?
Remember I told you that this system table will be removed from a future version of SQL Server? If you were thinking of diving into some scripting using this table, don’t. Instead use sys.server_principals system view:
SELECT * FROM sys.server_principals
The first thing you should notice is that the number of rows between syslogins and server_principals is not identical. I’ll leave it as an exercise for you to see which ones are different.
You can also use the sys.sql_logins security catalog view, which inherits all the columns from sys.server_principals and adds a few extras. As the name suggests, however, you only get back SQL logins, not Windows logins. Go ahead, and see for yourself:
SELECT * FROM sys.sql_logins
If you are not collecting these details nightly, start doing it immediately. It will be worth it. Trust me.
See you tomorrow!
Cool topic. Though it doesn’t exactly put me in a college bball frame of mind… Do you know why the ability to query directly is being removed?
Claire,
They are legacy objects and msft wants users to start using the system views.
Good start, Thomas. You are wise to not do a 30 day series, btw… It is a lot of work!
Glenn,
Thanks! I didn’t like the thought of 30 posts either, needed to find a more unique way and Lent was longer!
Tom,
A great start to what will no doubt be an interesting and revealing series. I remember the day I discovered system tables, and I will say that was a wonderful day.
Thanks, and I’m looking forward to seeing the rest of the entries!
Dustin,
Thanks for reading!
Tom-
A quick gotcha on the sys.sql_logins view. It only shows SQL logins, not any AD/Windows pass-through accounts. If you want the whole story, you need the sys.server_principals view.
And yes, system views are AWESOME. Looking forward to the series.
Mike,
Thanks! I added this line:
“As the name suggests, however, you only get back SQL logins, not Windows logins.”
I hope that clears up any confusion.
Tom
Thanks for this….
I have been using sp_help_revlogin to get password hash in SQL 2005.
In SQL 2000 I use select * from sysxlogins but couldn’t get it with syslogins on SQL 2005.
Now I will use sys.sql_logins.
thanks again.
These are really cool I’m in the process of printing out the whole series. Allot of really applicable stuff in here that you want to know about when the shit hits the fan.
Also, like Glen said I’m sure this was allot of work, thanks for the helpful resource.