TSQL Tuesday #60: Something New Learned – What permissions does <username> have?

5

November 11, 2014 by Kenneth Fisher

T-SQL TuesdayWhat 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 = ”.

5 thoughts on “TSQL Tuesday #60: Something New Learned – What permissions does <username> have?

  1. Chris Yates says:

    Thanks for taking the time to participate and a stellar post some nice take-a-ways. Will be included in the roundup shortly.

  2. […] Kenneth Fisher (B|T) – provides a security gem you will want to read along with some practical examples of tracking down user perms and AD groups. […]

  3. J DBA says:

    Thanks for the post, it really helped me fix a problem (Error: 18456, Severity: 14, State: 11.) with domain users access to one of our SQL Servers. Particularly the Step 2 Query, where group membership is revealed. While everything we looked at told us there should be no problem connecting, this query revealed a Local Group that was the culprit denying “Connect SQL” access.

  4. […] I learned something new recently that has been a big help. There is a system view called sys.login_token that among other things returns all of the AD groups that the current security context belongs to. So how does that help us? It only returns our windows groups after all. Ahh, but when you add in the magic of EXECUTE AS which allows us to change our security context, we can pull the list for any login. By dumping the information into temp tables we can then compare them and get a single list of AD groups that they all belong to. […]

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: