What have I learned recently? Well as it happens I learned something rather interesting recently and was actually trying to figure out how/when to post it. This month’s T-SQL Tuesday host Chris Yates (b/t) wants to know something we have learned recently so that seems like perfect timing to me. Of course I try to learn something new as often as possible so it’s one subject that I would have always found convenient.

So to start with where did I learn this little nugget? A blog. And interestingly enough not a blog that I subscribe to on a regular basis. There are so many great blogs out there than you have to pick and choose who/what you are going to read. One of the things I’ve done is to pick a small group to follow and read on a regular basis and then I subscribed to one of the mailing lists on Toad World. This particular mailing list sends me emails about a number of different blogs that in some cases I read occasionally and in some I’ve never seen before. Quick aside here: I also get a similar list from skimming the SQL Server Central blog page. When I have time I skim the titles, and maybe a few words in, and if it seems interesting I read it. FYI If I’m not reading your blog it doesn’t mean it’s not a great blog, it just means I probably didn’t have time.

One of the things that truly interests me these days is security. In large part this is because I get a lot, and I mean a LOT, of security requests at work right now. These range from “Please grant me security to this instance just this other user has.” (“Uh, no. I need more detail than that.”) to “What permissions does this individual have on this instance.” (“Between individual permissions and dozens of AD groups what a pain.”)

Now I’m sure that ya’ll all have nice clean security. SQL Logins are very limited. Everyone is in an AD group or 3 and all permissions are granted through those AD groups.

Wait you mean it’s not? Individuals have dozens of AD groups granted over multiple years and multiple positions in the company? Individual permissions are granted all over the place when someone got lazy? Gosh, I never would have guessed.

So when, in my reading, I ran across a post claiming “determine the windows groups for a sql server login” you can understand why I was excited! As it happens it’s one of my favorite kinds of posts to read. Short with a very concrete example. I’m going to give a bit longer of an example here with a very specific application.

This example answers the question of “What permissions does <username> have on <instancename> and/or <datbasename>.” Before this I would have to get a list of all AD groups that have permissions, then go into the AD Users application look up the user and scan through literally dozens if not a hundred or more AD groups that the individual belongs to.

Step 1: Finding the users individual permissions

EXEC sp_dbpermissions @DBName = 'All', @Principal = '<UserName>'

In case you haven’t gotten a chance to play with my sp_dbpermission and sp_srvpermissions scripts they are designed to do security research and will list permissions at the user/login, role membership and individual permissions levels all at one time. If you pass in ‘All’ as the DBName for sp_dbpermissions then it will check and list the permissions information from all of the databases at once.

Step 2: What AD groups could the user have access through

EXECUTE AS LOGIN = '<loginname>'
SELECT token.name AS GroupNames
FROM sys.login_token token
JOIN sys.server_principals grp
ON token.sid = grp.sid
WHERE token.[type] = 'WINDOWS GROUP'
  AND grp.[type] = 'G'
REVERT

Here is where I learned something new; sys.login_token This particular system view (which has been around since SQL 2005) lists all of the principals associated with the current login. This includes (among other things) a list of AD groups the login belongs to. By changing our excution context using the EXECUTE AS statement we can link sys.login_token and sys.server_principals to get a list of every AD group that the login is a member of and has access to the instance.

Step 3: What permissions does each AD group have.

For each of the AD groups identified above run the following.

EXEC sp_dbpermissions @DBName = 'All', @Principal = '<groupname>'

By combining the results of step 1 and step 3 (remembering that a deny at any level will override anything except a DB Owner or Sysadmin) you can get a complete list of a logins permissions. Of course I should point out if you have any user defined roles you can continue deeper by running sp_dbpermissions against each of the role names by listing it as the @Principal. Also if you need to look at the permissions for a single database just change @DBName = ‘All’ to @DBName = ”.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tuesday Tagged: code language, database permissions, language sql, microsoft sql server, security, server permissions, T-SQL, T-SQL Tuesday