5
(1)

Below query can be used to find object level permission for specific user in SQL Server database.

use <DB>  -- replace database name here
go
select @@servername as Server, db_name() as 'Database', su.name as 'UserName', so.name as Object, permission_name as Permission from sys.database_permissions dp
join sys.sysusers su on grantee_principal_id = uid
join sys.sysobjects so on major_id = id
where su.name='<UserName>'    -- replace user name here

Example:

use Dbalyfe
go
select @@servername as Server, db_name() as 'Database', su.name as 'UserName', so.name as Object, permission_name as Permission from sys.database_permissions dp
join sys.sysusers su on grantee_principal_id = uid
join sys.sysobjects so on major_id = id
where su.name='Testuser2'

Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.