select * from (
\nSELECT
\n [UserName] = CASE princ.[type]
\n WHEN ‘S’ THEN princ.[name]
\n WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI
\n END,
\n [UserType] = CASE princ.[type]
\n WHEN ‘S’ THEN ‘SQL User’
\n WHEN ‘U’ THEN ‘Windows User’
\n END,
\n [DatabaseUserName] = princ.[name],
\n [Role] = null,
\n [PermissionType] = perm.[permission_name],
\n [PermissionState] = perm.[state_desc],
\n [ObjectType] = obj.type_desc,–perm.[class_desc],
\n [ObjectName] = OBJECT_NAME(perm.major_id),
\n [ColumnName] = col.[name]
\nFROM
\n –database user
\n sys.database_principals princ
\nLEFT JOIN
\n –Login accounts
\n sys.login_token ulogin on princ.[sid] = ulogin.[sid]
\nLEFT JOIN
\n –Permissions
\n sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
\nLEFT JOIN
\n –Table columns
\n sys.columns col ON col.[object_id] = perm.major_id
\n AND col.[column_id] = perm.[minor_id]
\nLEFT JOIN
\n sys.objects obj ON perm.[major_id] = obj.[object_id]
\nWHERE
\n princ.[type] in (‘S’,’U’)
\nUNION
\n–List all access provisioned to a sql user or windows user\/group through a database or application role
\nSELECT
\n [UserName] = CASE memberprinc.[type]
\n WHEN ‘S’ THEN memberprinc.[name]
\n WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI
\n END,
\n [UserType] = CASE memberprinc.[type]
\n WHEN ‘S’ THEN ‘SQL User’
\n WHEN ‘U’ THEN ‘Windows User’
\n END,
\n [DatabaseUserName] = memberprinc.[name],
\n [Role] = roleprinc.[name],
\n [PermissionType] = perm.[permission_name],
\n [PermissionState] = perm.[state_desc],
\n [ObjectType] = obj.type_desc,–perm.[class_desc],
\n [ObjectName] = OBJECT_NAME(perm.major_id),
\n [ColumnName] = col.[name]
\nFROM
\n –Role\/member associations
\n sys.database_role_members members
\nJOIN
\n –Roles
\n sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
\nJOIN
\n –Role members (database users)
\n sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
\nLEFT JOIN
\n –Login accounts
\n sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
\nLEFT JOIN
\n –Permissions
\n sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
\nLEFT JOIN
\n –Table columns
\n sys.columns col on col.[object_id] = perm.major_id
\n AND col.[column_id] = perm.[minor_id]
\nLEFT JOIN
\n sys.objects obj ON perm.[major_id] = obj.[object_id]
\nUNION
\n–List all access provisioned to the public role, which everyone gets by default
\nSELECT
\n [UserName] = ‘{All Users}’,
\n [UserType] = ‘{All Users}’,
\n [DatabaseUserName] = ‘{All Users}’,
\n [Role] = roleprinc.[name],
\n [PermissionType] = perm.[permission_name],
\n [PermissionState] = perm.[state_desc],
\n [ObjectType] = obj.type_desc,–perm.[class_desc],
\n [ObjectName] = OBJECT_NAME(perm.major_id),
\n [ColumnName] = col.[name]
\nFROM
\n –Roles
\n sys.database_principals roleprinc
\nLEFT JOIN
\n –Role permissions
\n sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
\nLEFT JOIN
\n –Table columns
\n sys.columns col on col.[object_id] = perm.major_id
\n AND col.[column_id] = perm.[minor_id]
\nJOIN
\n –All objects
\n sys.objects obj ON obj.[object_id] = perm.[major_id]
\nWHERE
\n –Only roles
\n roleprinc.[type] = ‘R’ AND
\n –Only public role
\n roleprinc.[name] = ‘public’ AND
\n –Only objects of ours, not the MS objects
\n obj.is_ms_shipped = 0
\n) T
\nwhere T.DatabaseUserName like ‘%HQU-PlatFin-DBAdmin%’<\/p>\n","protected":false},"excerpt":{"rendered":"
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] = … Continue reading