SQL Server: T-SQL Script to find object level permission for specific user dbalyfe

25 Sep, 2022

SQL Server: T-SQL Script to find object level permission for specific user

MSSQL, T-SQL Scripts database access, object level permission, SQL server, table level permission, TSQL

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: