1

We have MS SQL Server 2012 in production use and MS AD.

Let me explain in short what our concept of assigning user permissions is like.

For certain user group that has some access to a certain application we create AD group and assign AD users to it. Since this application needs to access data in other DB, we add that group to that DB as well and set access/execute rights to needed objects (either specific object or schemas).

Since certain people use other applications and DBs, they are added to other AD groups as well. Those groups, by no surprise, need to access some objects to second database too.

So we have situation when certain users are in few different AD groups with different accesses to the same objects in a DB. That causes lots of "strange" behaviors when, all of a sudden, some user gets denied access to objects in DB.

My questions are:

Is there any way to find out with which privileges (through which AD group) some user got access/denial for certain sql query that was triggered from application?

Can someone explain how sql server handles privileges in such environment?

I'm open for any suggestions on how to handle/set accesses differently altogether but not involving reprogramming applications (only DBA site).

Thank you.

j_j
  • 11
  • 2

1 Answers1

0

Assuming that you're an admin, you can look at sys.login_token, sys.user_token, and sys.fn_my_permissions() while impersonating the user in question to get some idea. Something like:

execute as login='yourDom\User1';
select * from sys.login_token;
select * from sys.user_token;
select * from sys.fn_my_permission('dbo.someObject', 'OBJECT');
revert;
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thank you for your answer Ben. I'll surely look into it. As far as I get it, what I need involves lots of digging. But you gave me a good direction. As far as it goes for fn_my_permission, functions' name lacks "s" on the end. – j_j Mar 11 '16 at 09:49
  • Does impersonation work on a user that is added to SQL server merely through AD group? – j_j Mar 11 '16 at 09:59