Labels

Monday, 18 November 2019

How to find object level permissions of single user in SQL Server ?


DECLARE @Username VARCHAR(100)

SET @Username = 'UserName'

SELECT *
FROM (
 SELECT [UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U'
 THEN ulogin.[name] COLLATE Latin1_General_CI_AI END
  ,[UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' 
THEN 'Windows User' END
  ,[DatabaseUserName] = princ.[name]
  ,[Role] = NULL
  ,[PermissionType] = PERM.[permission_name]
  ,[PermissionState] = PERM.[state_desc]
  ,[ObjectType] = obj.type_desc
  ,--perm.[class_desc],       
  [ObjectName] = OBJECT_NAME(PERM.major_id)
  ,[ColumnName] = col.[name]
 FROM
  --database user
  sys.database_principals princ
 LEFT JOIN
  --Login accounts
  sys.login_token ulogin ON princ.[sid] = ulogin.[sid]
 LEFT JOIN
  --Permissions
  sys.database_permissions PERM ON PERM.[grantee_principal_id] = princ.[principal_id]
 LEFT JOIN
  --Table columns
  sys.columns col ON col.[object_id] = PERM.major_id
  AND col.[column_id] = PERM.[minor_id]
 LEFT JOIN sys.objects obj ON PERM.[major_id] = obj.[object_id]
 WHERE princ.[type] IN (
   'S'
   ,'U'
   )
 
 UNION
 
 --List all access provisioned to a sql user or windows user/group through a database 
or application role
 SELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' 
THEN ulogin.[name] COLLATE Latin1_General_CI_AI END
  ,[UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' 
THEN 'Windows User' END
  ,[DatabaseUserName] = memberprinc.[name]
  ,[Role] = roleprinc.[name]
  ,[PermissionType] = PERM.[permission_name]
  ,[PermissionState] = PERM.[state_desc]
  ,[ObjectType] = obj.type_desc
  ,--perm.[class_desc],   
  [ObjectName] = OBJECT_NAME(PERM.major_id)
  ,[ColumnName] = col.[name]
 FROM
  --Role/member associations
  sys.database_role_members members
 INNER JOIN
  --Roles
  sys.database_principals roleprinc
 ON roleprinc.[principal_id] = members.[role_principal_id]
 INNER JOIN
  --Role members (database users)
  sys.database_principals memberprinc 
ON memberprinc.[principal_id] = members.[member_principal_id]
 LEFT JOIN
  --Login accounts
  sys.login_token ulogin ON memberprinc.[sid] = ulogin.[sid]
 LEFT JOIN
  --Permissions
  sys.database_permissions PERM ON PERM.[grantee_principal_id] = roleprinc.[principal_id]
 LEFT JOIN
  --Table columns
  sys.columns col ON col.[object_id] = PERM.major_id
  AND col.[column_id] = PERM.[minor_id]
 LEFT JOIN sys.objects obj ON PERM.[major_id] = obj.[object_id]
 
 UNION
 
 --List all access provisioned to the public role, which everyone gets by default
 SELECT [UserName] = '{All Users}'
  ,[UserType] = '{All Users}'
  ,[DatabaseUserName] = '{All Users}'
  ,[Role] = roleprinc.[name]
  ,[PermissionType] = PERM.[permission_name]
  ,[PermissionState] = PERM.[state_desc]
  ,[ObjectType] = obj.type_desc
  ,--perm.[class_desc],  
  [ObjectName] = OBJECT_NAME(PERM.major_id)
  ,[ColumnName] = col.[name]
 FROM
  --Roles
  sys.database_principals roleprinc
 LEFT JOIN
  --Role permissions
  sys.database_permissions PERM ON PERM.[grantee_principal_id] = roleprinc.[principal_id]
 LEFT JOIN
  --Table columns
  sys.columns col ON col.[object_id] = PERM.major_id
  AND col.[column_id] = PERM.[minor_id]
 INNER JOIN
  --All objects   
  sys.objects obj ON obj.[object_id] = PERM.[major_id]
 WHERE
  --Only roles
  roleprinc.[type] = 'R'
  AND
  --Only public role
  roleprinc.[name] = 'public'
  AND
  --Only objects of ours, not the MS objects
  obj.is_ms_shipped = 0
 ) a
WHERE username = @Username

No comments:

Post a Comment