Find objects (views, procedures) for which on person has permissions but another does not
I need to find all of the SQL Server (2008 R2) objects which one user has SELECT or Execute permissions on which another user doesn't have the same permissions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
If your users get permissions only from built-in db roles or by specific grants to their individual user id, this won't be too bad. But if you use other ways to grant access, this could much more complex very quickly.
ASKER
Scott,
That is one of the points of this operation, to see what permissions have been granted to a particular role vs a particular user.
@Russ
I think, that if I create two CTEs based on the above query, I should be able to join them in a Full Outer Join on the Schema, Object, and Permission and then display the two users as separate columns to indicate which is assigned that particular permission. Will user roles show up in the [Principal_Name] column?
What I really want to do here is identify these objects/permissions and make sure they are assigned to a role that both of these users are assigned to. Then I should be able to remove the individual permissions for each of the users.
That is one of the points of this operation, to see what permissions have been granted to a particular role vs a particular user.
@Russ
I think, that if I create two CTEs based on the above query, I should be able to join them in a Full Outer Join on the Schema, Object, and Permission and then display the two users as separate columns to indicate which is assigned that particular permission. Will user roles show up in the [Principal_Name] column?
What I really want to do here is identify these objects/permissions and make sure they are assigned to a role that both of these users are assigned to. Then I should be able to remove the individual permissions for each of the users.
Yes, roles show up in the [sys].[database_principals
I'm not sure why you would need a CTE for this. Perhaps I don't fully understand what you're trying to do.
I'm not sure why you would need a CTE for this. Perhaps I don't fully understand what you're trying to do.
ASKER
basically I want to create a pivot query, and I hate having to write those in SQL Server (Access syntax is so much easier). So, I will frequently simply create 2 CTEs with different critieria, then join those with a Full outer join and pull in all of the linking fields (in this case schema, object, and permission) and then the Principal_Name from each with the column alias according to the criteria used for each.
I also hate pivot queries so you have my blessing. ;)
It sounds like your approach will work. It'll just take a little tweaking to get the actual data you want.
It sounds like your approach will work. It'll just take a little tweaking to get the actual data you want.
Sorry, I wasn't clear enough.
Do you use user-defined roles in SQL? The issue then becomes that a role can be granted another role, and they can be stacked arbitrarily deep. That can make determining the full permissions of user_1 that has been granted role_a, when role_a has been granted role_b and role_c, and role_c has been granted...
Again, if you don't have user-defined roles, then the joins are known to be much simpler and you can use simple joins on basic system views to get what you want.
Do you use user-defined roles in SQL? The issue then becomes that a role can be granted another role, and they can be stacked arbitrarily deep. That can make determining the full permissions of user_1 that has been granted role_a, when role_a has been granted role_b and role_c, and role_c has been granted...
Again, if you don't have user-defined roles, then the joins are known to be much simpler and you can use simple joins on basic system views to get what you want.
ASKER
Scott,
No user defined roles, so I think Russ's solution will meet my needs.
Dale
No user defined roles, so I think Russ's solution will meet my needs.
Dale
ASKER
Thanks, Russ, this worked great