audit-labs/audit-tools

  1/*
  2Security Audit Report
  31) List all access provisioned to a sql user or windows user/group directly
  42) List all access provisioned to a sql user or windows user/group through a database or application role
  53) List all access provisioned to the public role
  6
  7Columns Returned:
  8UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
  9UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
 10                  SQL Server user account.
 11DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
 12                  same as the server user.
 13Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
 14                  on the user account, otherwise this will be the name of the role that the user is a member of.
 15PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
 16                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
 17                  This value may not be populated for all roles.  Some built in roles have implicit permission
 18                  definitions.
 19PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
 20                  This value may not be populated for all roles.  Some built in roles have implicit permission
 21                  definitions.
 22ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
 23                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
 24                  This value may not be populated for all roles.  Some built in roles have implicit permission
 25                  definitions.
 26ObjectName      : Name of the object that the user/role is assigned permissions on.
 27                  This value may not be populated for all roles.  Some built in roles have implicit permission
 28                  definitions.
 29ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
 30                  is only populated if the object is a table, view or a table value function.
 31*/
 32
 33--List all access provisioned to a sql user or windows user/group directly
 34SELECT
 35    [UserName] = CASE princ.[type]
 36                    WHEN 'S' THEN princ.[name]
 37                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
 38                 END,
 39    [UserType] = CASE princ.[type]
 40                    WHEN 'S' THEN 'SQL User'
 41                    WHEN 'U' THEN 'Windows User'
 42                 END,
 43    [DatabaseUserName] = princ.[name],
 44    [Role] = null,
 45    [PermissionType] = perm.[permission_name],
 46    [PermissionState] = perm.[state_desc],
 47    [ObjectType] = obj.type_desc,--perm.[class_desc],
 48    [ObjectName] = OBJECT_NAME(perm.major_id),
 49    [ColumnName] = col.[name]
 50FROM
 51    --database user
 52    sys.database_principals princ
 53LEFT JOIN
 54    --Login accounts
 55    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
 56LEFT JOIN
 57    --Permissions
 58    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
 59LEFT JOIN
 60    --Table columns
 61    sys.columns col ON col.[object_id] = perm.major_id
 62                    AND col.[column_id] = perm.[minor_id]
 63LEFT JOIN
 64    sys.objects obj ON perm.[major_id] = obj.[object_id]
 65WHERE
 66    princ.[type] in ('S','U')
 67UNION
 68--List all access provisioned to a sql user or windows user/group through a database or application role
 69SELECT
 70    [UserName] = CASE memberprinc.[type]
 71                    WHEN 'S' THEN memberprinc.[name]
 72                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
 73                 END,
 74    [UserType] = CASE memberprinc.[type]
 75                    WHEN 'S' THEN 'SQL User'
 76                    WHEN 'U' THEN 'Windows User'
 77                 END,
 78    [DatabaseUserName] = memberprinc.[name],
 79    [Role] = roleprinc.[name],
 80    [PermissionType] = perm.[permission_name],
 81    [PermissionState] = perm.[state_desc],
 82    [ObjectType] = obj.type_desc,--perm.[class_desc],
 83    [ObjectName] = OBJECT_NAME(perm.major_id),
 84    [ColumnName] = col.[name]
 85FROM
 86    --Role/member associations
 87    sys.database_role_members members
 88JOIN
 89    --Roles
 90    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
 91JOIN
 92    --Role members (database users)
 93    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
 94LEFT JOIN
 95    --Login accounts
 96    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
 97LEFT JOIN
 98    --Permissions
 99    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
100LEFT JOIN
101    --Table columns
102    sys.columns col on col.[object_id] = perm.major_id
103                    AND col.[column_id] = perm.[minor_id]
104LEFT JOIN
105    sys.objects obj ON perm.[major_id] = obj.[object_id]
106UNION
107--List all access provisioned to the public role, which everyone gets by default
108SELECT
109    [UserName] = '{All Users}',
110    [UserType] = '{All Users}',
111    [DatabaseUserName] = '{All Users}',
112    [Role] = roleprinc.[name],
113    [PermissionType] = perm.[permission_name],
114    [PermissionState] = perm.[state_desc],
115    [ObjectType] = obj.type_desc,--perm.[class_desc],
116    [ObjectName] = OBJECT_NAME(perm.major_id),
117    [ColumnName] = col.[name]
118FROM
119    --Roles
120    sys.database_principals roleprinc
121LEFT JOIN
122    --Role permissions
123    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
124LEFT JOIN
125    --Table columns
126    sys.columns col on col.[object_id] = perm.major_id
127                    AND col.[column_id] = perm.[minor_id]
128JOIN
129    --All objects
130    sys.objects obj ON obj.[object_id] = perm.[major_id]
131WHERE
132    --Only roles
133    roleprinc.[type] = 'R' AND
134    --Only public role
135    roleprinc.[name] = 'public' AND
136    --Only objects of ours, not the MS objects
137    obj.is_ms_shipped = 0
138ORDER BY
139    princ.[Name],
140    OBJECT_NAME(perm.major_id),
141    col.[name],
142    perm.[permission_name],
143    perm.[state_desc],
144    obj.type_desc--perm.[class_desc]