diff options
Diffstat (limited to 'databases/sql/admins.sql')
| -rw-r--r-- | databases/sql/admins.sql | 144 |
1 files changed, 144 insertions, 0 deletions
diff --git a/databases/sql/admins.sql b/databases/sql/admins.sql new file mode 100644 index 0000000..278fafc --- /dev/null +++ b/databases/sql/admins.sql @@ -0,0 +1,144 @@ +/* +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 account. 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] |