Find objects (views, procedures) for which on person has permissions but another does not

Dale Fye
Dale Fye used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Commented:
This query will give you information about which objects have which permissions granted
SELECT 
	[object_id],
	[schemas].[name] AS [Schema_Name],
	[objects].[name] AS [Object_Name],
	[database_principals].[name] AS [Principal_Name],
	[database_permissions].[permission_name]
FROM 
	[sys].[database_permissions]
INNER JOIN
	[sys].[database_principals] ON
	[sys].[database_principals].[principal_id] = [sys].[database_permissions].[grantee_principal_id]
INNER JOIN
	[sys].[objects] ON
	[sys].[objects].[object_id] = [sys].[database_permissions].[major_id]
INNER JOIN
	[sys].[schemas] ON
	[sys].[schemas].[schema_id] = [sys].[objects].[schema_id]

Open in new window

If you need something more specific then you'll need to include some kind of WHERE clause which says where one user exists but another does not.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Russ SuterSenior Software Developer

Commented:
Yes, roles show up in the [sys].[database_principals[ table as type "R" and will be included in the query above. To determine which users belong to which roles you can query the [sys].[database_role_members] table. You'll probably want to join [sys].[database_principals] on that to make it readable as that table only contains 2 columns, role_principal_id and member_principal_id.

I'm not sure why you would need a CTE for this. Perhaps I don't fully understand what you're trying to do.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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.
Russ SuterSenior Software Developer

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Scott,

No user defined roles, so I think Russ's solution will meet my needs.

Dale
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, Russ, this worked great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial