Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Security Audit Report
- 1) List all access provisioned to a sql user or windows user/group directly
- 2) List all access provisioned to a sql user or windows user/group through a database or application role
- 3) List all access provisioned to the public role
- Columns Returned:
- UserName : SQL or Windows/Active Directory user cccount. This could also be an Active Directory group.
- UserType : Value will be either 'SQL User' or 'Windows User'. This reflects the type of user defined for the
- SQL Server user account.
- DatabaseUserName: Name of the associated user as defined in the database user account. The database user may not be the
- same as the server user.
- Role : The role name. This will be null if the associated permissions to the object are defined at directly
- on the user account, otherwise this will be the name of the role that the user is a member of.
- PermissionType : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
- DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
- This value may not be populated for all roles. Some built in roles have implicit permission
- definitions.
- PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
- This value may not be populated for all roles. Some built in roles have implicit permission
- definitions.
- ObjectType : Type of object the user/role is assigned permissions on. Examples could include USER_TABLE,
- SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
- This value may not be populated for all roles. Some built in roles have implicit permission
- definitions.
- ObjectName : Name of the object that the user/role is assigned permissions on.
- This value may not be populated for all roles. Some built in roles have implicit permission
- definitions.
- ColumnName : Name of the column of the object that the user/role is assigned permissions on. This value
- is only populated if the object is a table, view or a table value function.
- */
- --List all access provisioned to a sql user or windows user/group directly
- 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
- JOIN
- --Roles
- sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
- 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]
- 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
- ORDER BY
- princ.[Name],
- OBJECT_NAME(perm.major_id),
- col.[name],
- perm.[permission_name],
- perm.[state_desc],
- obj.type_desc--perm.[class_desc]
- select princ.name
- , princ.type_desc
- , perm.permission_name
- , perm.state_desc
- , perm.class_desc
- , object_name(perm.major_id)
- from sys.database_principals princ
- left join
- sys.database_permissions perm
- on perm.grantee_principal_id = princ.principal_id
Add Comment
Please, Sign In to add comment