Peek-a-Who? Ha! I See You!

When I started blogging, my goal was to put up a new post at least once a week, and I feel I’ve pretty much kept to it. These last few weeks have been an exception with the SQL Cruise (ah, fun times) followed immediately by family visiting from out of state (do not stop at Go, do not collect $200… it really wasn’t that bad), readjusting to work life (umm… yeah… about that work request…), getting ready for Kindergarten (our daughter, not me), and so on and so forth. By the time it was all said and done, I was pretty darn tired. So I took a bit of a break and didn’t do a post last Friday like I usually do. Now that I’ve had time to regroup and refocus a bit, it’s time to get the ball rolling again… and hopefully not rolling into a server! Did I just jinx myself?

Several weeks ago, I started blogging about the script I wrote several years ago to check the status of my SQL servers. Yes, it needs some serious updating, which I will get to soon. The good news is that not only did I learn some really cool things about DMOs (Dynamic Management Objects) from Tim Ford (a.k.a. SQL Agent Man Blog | Twitter) while on SQL Cruise, but I really believe I can apply some DMVs (Dynamic Management Views) and DMFs (Dynamic Management Functions) to my script! Now the bad news. It won’t be in this week’s post. I know! I know! I’m disappointed too! Why not this post, you ask? That, my friend, is because this week’s post is all about monitoring the logins. More specifically, keeping an eye on who has sysadmin privileges (no, I’m not paranoid… not at all), which logins have their default database set to master (for shame!), and any new logins created in the last month (who the heck are you?).

I have two versions in the same script. One is for SQL Server 2005+ and the other for SQL Server 2000. Yes, 2000. That’s because not all our systems can be up upgraded any time soon for various reasons (e.g. limited budget, time, vendor demands, the sky isn’t falling, etc.). This was the case at my last job as well.

Disclaimer: If you decide to try the code out, please feel free to do so. However, please try it out on a development or test server first to ensure it’ll run okay in your environment. I am not responsible for any mishaps (i.e. coffee spillage and so on) that may occur while running said code.

For the SQL 2005+ version, the two main catalog views (not tables) I use for this are sys.server_role_members and sys.server_principals. What the heck is a catalog view, you may be asking? In SQL 2005+, catalog views provide a generic, efficient interface to the underlying metadata. Why not just use the system tables? It is possible the system tables could change in future releases. If you use the catalog views instead, you probably won’t have to change your code to adapt, hopefully.

The sys.server_role_members catalog view just returns the id of who is a member of each fixed role. The sys.server_principals catalog view returns information about each principal. What do you mean by principal? A principal can be a Windows domain or local login, a SQL Server login, or a database user or role. Join them together, add some criteria, and voila! It will show you who has server-level permissions such as sysadmin. The code is below.

SQL Server 2005+:
---Check admin logins (ALL)...
select p.name 'role'
,m.name 'login'
,m.create_date
,datediff(d,m.create_date,getdate()) 'created_nbr_of_days_ago'
,m.modify_date
,cast(datediff(d,m.modify_date,getdate()) as varchar(5)) 'upd_days'
,m.default_database_name
from master.sys.server_role_members r
join master.sys.server_principals p
on (r.role_principal_id=p.principal_id)
join master.sys.server_principals m
on (r.member_principal_id=m.principal_id)
where p.type='R'
order by m.create_date desc, m.modify_date desc

The “type” field in the where clause describes what kind of principal it is such as a server role, a SQL login, a Windows login, a Windows group, and so on. I’ve set it here to “R” to see the fixed server roles. I’ve also written it to show me when the login was created or modified. You can always modify it to only return recently created or modified logins (principals).

SQL Server 2000:

---Check admin logins (ALL)...
select [name]
,createdate
,datediff(d,createdate,getdate()) 'created_nbr_of_days_ago'
,updatedate
,cast(datediff(d,updatedate,getdate()) as varchar(5)) 'upd_days'
,dbname
,loginname
,sysadmin
,securityadmin
,serveradmin
,setupadmin
,processadmin
,diskadmin
,dbcreator
,bulkadmin
,hasaccess
,isntname
,isntgroup
,isntuser
from master.sys.syslogins
where (sysadmin=1 or securityadmin=1 or setupadmin=1 or processadmin=1 or diskadmin=1 or dbcreator=1 or bulkadmin=1)
order by createdate desc, updatedate desc

As you can see, there’s just a slight difference between my SQL 2005+ and SQL 2000 scripts. 🙂  The 2000 script is fairly self explanatory. In this script, I’m basically checking for logins that are assigned to any of the server-level roles.

Now to check logins that have the default database set to master. Why is this a bad thing? The master database is essential to SQL Server. Not only does it contain information about all the databases, but all the logins as well. You don’t want just anyone poking around in there unless you really don’t have a choice. Here are the scripts:

SQL Server 2005+:

--Check logins where the default db is master...
select [name]
,create_date
,modify_date
,default_database_name
from master.sys.server_principals
where default_database_name='master'
order by create_date desc, modify_date desc
 

SQL Server 2000:
--Check logins where the default db is master...
select [name]
,createdate
,updatedate
,dbname
,[password]
from master.sys.syslogins
where dbname='master'
order by createdate desc, updatedate desc

I also like to check if there are any new or modified logins from the last month.

SQL Server 2005+:
–Check logins updated in the last 28 days …
select [name]
,create_date
,modify_date
,datediff(d,create_date,getdate()) ‘created_nbr_of_days_ago’
,default_database_name
from master.sys.server_principals
where datediff(d,create_date,getdate())<= 28 
order by modify_date desc

SQL Server 2000:
–Check logins updated in the last 28 days …
select name
,createdate
,updatedate
,datediff(d,createdate,getdate()) ‘created_nbr_of_days_ago’
,dbname
,[password]
from master.sys.syslogins
where datediff(d,createdate,getdate())<= 28
order by updatedate desc

I like to check the password field for SQL logins to see if it’s NULL, which is a big no-no. In SQL 2000, it’s fairly easy to check. To check for blank (null) password fields in SQL 2005+ is much trickier. K. Brian Kelley wrote a nice post on how to do this on SQLServerCentral.com.

I could actually combine some of these queries together which is fine. However, I like to keep them simple and separate so I know what I’m looking at right away. I usually run a basic select statement before the queries indicating what the results are, such as “select logins that are in the sysadmin role” then that specific query runs right after it. When I’m in a bit of a hurry, it saves me time.

We will be looking into using Policy Based Management soon which will help us tremendously considering we now have 60+ SQL Server instances we have to manage with 3 DBAs (one being our manager).

Next week is all about processes. This is where I desperately need to update my script using super duper cool DMOs (Dynamic Management Objects). I’ll include my updated code. Woo hoo!!! 🙂 

Updated: To view the prior post in this series, please refer to “The Bermuda Cluster”. Next up is “I Distinctly Detect a Percolating Process”.

Advertisements

5 thoughts on “Peek-a-Who? Ha! I See You!

  1. Very nice Your Highness. I’d rather not comment about the revelations encountered when running these except one… I couldn’t believe the “created_nbr_of_days_ago” value for my login. I’ve been in this chair for a while! By the way, it sounds like you don’t use master for the default database, so how do you handle this? There was some chatter on twitter about this several months ago and it seemed like lots of people didn’t have a problem with master being the default.

    1. Thanks, Noel! 🙂 Well, for us admins our logins have a default database of master which is okay since we trust ourselves (hopefully). I could be wrong in this, but I’ve learned and have been told by more senior DBAs that in general, it’s not good for user logins to default to the master database. What if someone has permissions to create new objects and accidentally does so in master or start tinkering around with other things? Chances are most people probably wouldn’t tinker around and it may be okay. However, I would say that it really does depend on your environment and your applications. At my last job, we had an application where the users’ default database HAD to be master because that’s where all the application’s objects resided AND it was on the main cluster! I wasn’t thrilled about it because some of their logins had permissions to edit objects. Once I found out, I had to be very careful in limiting their permissions as much as I could because they were in the master database. I couldn’t move them to another server either which is a long fun story. Anyway, I’m not sure if I was able to answer your question. I can see both sides of it and personally I don’t like the idea of anyone defaulting to the master database unless they know what they are doing and can be trusted. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s