/*
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:
UserType : Value will be either 'SQL User', 'Windows User', or 'Windows Group'.
This reflects the type of user/group defined for the SQL Server 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.
LoginName : SQL or Windows/Active Directory user account. This could also be an Active Directory group.
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.
Schema : Name of the schema the object is in.
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.
*/
--1) List all access provisioned to a SQL user or Windows user/group directly
SELECT
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Database user
sys.database_principals AS princ
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G')
-- No need for these system accounts
AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
[UserType] = CASE membprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--Permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN ('S','U','G')
-- No need for these system accounts
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[LoginName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
--All objects
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R'
AND roleprinc.[name] = 'public'
AND obj.[is_ms_shipped] = 0
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users)
/*
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.
*/
DECLARE @userName NVARCHAR(4) = @p_UserName + '%'
--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')
AND princ.[name] LIKE @userName -- Added this line --CSLAGLE
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]
WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE
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]
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
@@servername ServerName
, db_name() DatabaseName
, [UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[LoginName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals AS roleprinc
--Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
--All objects
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R'
AND roleprinc.[name] = 'public'
AND isnull(obj.[is_ms_shipped], 0) = 0
AND isnull(object_schema_name(perm.[major_id]), '') <> 'sys'
ORDER BY
[UserType],
[DatabaseUserName],
[LoginName],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals 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]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
/*
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]
/*
--Script source found at : http://stackoverflow.com/a/7059579/1387418
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.
PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName
PrincipalType : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group'
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.
*/
DECLARE @HideDatabaseDiagrams BIT = 1;
--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
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE princ.[type]
END,
[PrincipalUserName] = COALESCE(
CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
princ.[name]
),
[PrincipalType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN 'Database Role'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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')
AND CASE
WHEN @HideDatabaseDiagrams = 1 AND
princ.[name] = 'guest'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
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
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
END,
[PrincipalUserName] = COALESCE(
CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
WHEN 'R' THEN NULL -- Database Role
WHEN 'G' THEN NULL -- Windows Group
ELSE NULL
END,
memberprinc.[name]
),
[PrincipalType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'R' THEN 'Database Role'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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]
WHERE
CASE
WHEN @HideDatabaseDiagrams = 1 AND
memberprinc.[name] = 'guest'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[PrincipalUserName] = '{All Users}',
[PrincipalType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
[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
AND CASE
WHEN @HideDatabaseDiagrams = 1 AND
roleprinc.[name] = 'public'
AND (
(
obj.type_desc = 'SQL_SCALAR_FUNCTION'
AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
)
OR (
obj.type_desc = 'SQL_STORED_PROCEDURE'
AND OBJECT_NAME(perm.major_id) IN
(
N'sp_alterdiagram',
N'sp_creatediagram',
N'sp_dropdiagram',
N'sp_helpdiagramdefinition',
N'sp_helpdiagrams',
N'sp_renamediagram'
)
)
)
THEN 0
ELSE 1
END = 1
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]
8
从SQL Server 2005开始,您可以使用系统视图 . 例如,此查询列出数据库中的所有用户及其权限:
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
USE [master] ;
GO
IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
AND [type] in (N'P',N'PC')
)
BEGIN
DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#permission]')
)
DROP TABLE #permission
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
)
DROP TABLE #userroles_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
)
DROP TABLE #rolemember_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
)
DROP TABLE ##db_name
;
DECLARE
@db_name VARCHAR(255)
,@sql_text VARCHAR(MAX)
;
SET @sql_text =
'CREATE TABLE ##db_name
(
LoginUserName VARCHAR(MAX)
,'
;
DECLARE cursDBs CURSOR FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDBs
;
FETCH NEXT FROM cursDBs INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text =
@sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
,'
FETCH NEXT FROM cursDBs INTO @db_name
END
CLOSE cursDBs
;
SET @sql_text =
@sql_text + 'IsSysAdminLogin CHAR(1)
,IsEmptyRow CHAR(1)
)'
--PRINT @sql_text
EXEC (@sql_text)
;
DEALLOCATE cursDBs
;
DECLARE
@RoleName VARCHAR(255)
,@UserName VARCHAR(255)
;
CREATE TABLE #permission
(
LoginUserName VARCHAR(255)
,databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
DECLARE cursSysSrvPrinName CURSOR FOR
SELECT [name]
FROM sys.server_principals
WHERE
[type] IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND [name] NOT LIKE '##%'
ORDER BY [name]
;
OPEN cursSysSrvPrinName
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(255)
,membername VARCHAR(255)
,membersid VARBINARY(2048)
)
;
DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDatabases
;
DECLARE
@DBN VARCHAR(255)
,@sqlText NVARCHAR(4000)
;
FETCH NEXT FROM cursDatabases INTO @DBN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlText =
N'USE ' + QUOTENAME(@DBN) + ';
TRUNCATE TABLE #RoleMember_kk
INSERT INTO #RoleMember_kk
EXEC sp_helprolemember
INSERT INTO #UserRoles_kk
(DatabaseName,[Role])
SELECT db_name(),dbRole
FROM #RoleMember_kk
WHERE MemberName = ''' + @UserName + '''
'
--PRINT @sqlText ;
EXEC sp_executesql @sqlText ;
FETCH NEXT FROM cursDatabases INTO @DBN
END
CLOSE cursDatabases
;
DEALLOCATE cursDatabases
;
INSERT INTO #permission
SELECT
@UserName 'user'
,b.name
,u.[role]
FROM
sys.sysdatabases b
LEFT JOIN
#userroles_kk u
ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
ORDER BY 1
;
DROP TABLE #userroles_kk
;
DROP TABLE #rolemember_kk
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
END
CLOSE cursSysSrvPrinName
;
DEALLOCATE cursSysSrvPrinName
;
TRUNCATE TABLE ##db_name
;
DECLARE
@d1 VARCHAR(MAX)
,@d2 VARCHAR(MAX)
,@d3 VARCHAR(MAX)
,@ss VARCHAR(MAX)
;
DECLARE cursPermisTable CURSOR FOR
SELECT * FROM #permission
ORDER BY 2 DESC
;
OPEN cursPermisTable
;
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS
(
SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
)
BEGIN
SET @ss =
'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'
EXEC (@ss)
;
SET @ss =
'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''
EXEC (@ss)
;
END
ELSE
BEGIN
DECLARE
@var NVARCHAR(MAX)
,@ParmDefinition NVARCHAR(MAX)
,@var1 NVARCHAR(MAX)
;
SET @var =
N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
;
SET @ParmDefinition =
N'@var1 NVARCHAR(600) OUTPUT '
;
EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
;
SET @var1 =
ISNULL(@var1, ' ')
;
SET @var =
' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '
;
EXEC (@var)
;
END
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
END
CLOSE cursPermisTable
;
DEALLOCATE cursPermisTable
;
UPDATE ##db_name SET
IsSysAdminLogin = 'Y'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 1
;
DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM tempdb.sys.columns
WHERE
OBJECT_ID = OBJECT_ID('tempdb..##db_name')
AND [name] NOT IN ('LoginUserName','IsEmptyRow')
ORDER BY [name]
;
OPEN cursDNamesAsColumns
;
DECLARE
@ColN VARCHAR(255)
,@tSQLText NVARCHAR(4000)
;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'
--PRINT @tSQLText ;
EXEC sp_executesql @tSQLText ;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
END
CLOSE cursDNamesAsColumns
;
DEALLOCATE cursDNamesAsColumns
;
UPDATE ##db_name SET
IsEmptyRow = 'Y'
WHERE IsEmptyRow IS NULL
;
UPDATE ##db_name SET
IsSysAdminLogin = 'N'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 0
;
SELECT * FROM ##db_name
;
DROP TABLE ##db_name
;
DROP TABLE #permission
;
END TRY
BEGIN CATCH
DECLARE
@cursDBs_Status INT
,@cursSysSrvPrinName_Status INT
,@cursDatabases_Status INT
,@cursPermisTable_Status INT
,@cursDNamesAsColumns_Status INT
;
SELECT
@cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
;
IF @cursDBs_Status > -2
BEGIN
CLOSE cursDBs ;
DEALLOCATE cursDBs ;
END
IF @cursSysSrvPrinName_Status > -2
BEGIN
CLOSE cursSysSrvPrinName ;
DEALLOCATE cursSysSrvPrinName ;
END
IF @cursDatabases_Status > -2
BEGIN
CLOSE cursDatabases ;
DEALLOCATE cursDatabases ;
END
IF @cursPermisTable_Status > -2
BEGIN
CLOSE cursPermisTable ;
DEALLOCATE cursPermisTable ;
END
IF @cursDNamesAsColumns_Status > -2
BEGIN
CLOSE cursDNamesAsColumns ;
DEALLOCATE cursDNamesAsColumns ;
END
SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/
SELECT DB_NAME() AS database_name
, class
, class_desc
, major_id
, minor_id
, grantee_principal_id
, grantor_principal_id
, databasepermissions.type
, permission_name
, STATE
, state_desc
, granteedatabaseprincipal.name AS grantee_name
, granteedatabaseprincipal.type_desc AS grantee_type_desc
, granteeserverprincipal.name AS grantee_principal_name
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
, grantor.name AS grantor_name
, granted_on_name
, permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE
WHEN STATE = N'W'
THEN N' WITH GRANT OPTION'
ELSE N''
END AS permissionstatement
FROM (
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement
FROM sys.database_permissions
WHERE (sys.database_permissions.class = 0)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.objects
ON sys.objects.object_id = sys.database_permissions.major_id
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.objects.schema_id
LEFT OUTER JOIN sys.columns
ON sys.columns.object_id = sys.database_permissions.major_id
AND sys.columns.column_id = sys.database_permissions.minor_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 1)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.schemas.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.schemas
ON sys.schemas.schema_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 3)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(targetPrincipal.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.database_principals AS targetPrincipal
ON targetPrincipal.principal_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 4)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.assemblies.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.assemblies
ON sys.assemblies.assembly_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 5)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.types.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.types
ON sys.types.user_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 6)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.types.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.types
ON sys.types.user_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 6)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.xml_schema_collections
ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 10)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.service_message_types
ON sys.service_message_types.message_type_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 15)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.service_contracts
ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 16)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.services
ON sys.services.service_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 17)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.remote_service_bindings
ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 18)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.routes
ON sys.routes.route_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 19)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.symmetric_keys.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.symmetric_keys
ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 24)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.certificates.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.certificates
ON sys.certificates.certificate_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 25)
UNION ALL
SELECT sys.database_permissions.class
, sys.database_permissions.class_desc
, sys.database_permissions.major_id
, sys.database_permissions.minor_id
, sys.database_permissions.grantee_principal_id
, sys.database_permissions.grantor_principal_id
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name
, CASE
WHEN sys.database_permissions.state = N'W'
THEN N'GRANT'
ELSE sys.database_permissions.state_desc
END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement
FROM sys.database_permissions
INNER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id
WHERE (sys.database_permissions.major_id >= 0)
AND (sys.database_permissions.class = 26)
) AS databasepermissions
INNER JOIN sys.database_principals AS granteedatabaseprincipal
ON granteedatabaseprincipal.principal_id = grantee_principal_id
LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal
ON granteeserverprincipal.sid = granteedatabaseprincipal.sid
INNER JOIN sys.database_principals AS grantor
ON grantor.principal_id = grantor_principal_id
ORDER BY grantee_name, granted_on_name
SELECT roles.name AS role_name
, roles.principal_id
, roles.type AS role_type
, roles.type_desc AS role_type_desc
, roles.is_fixed_role AS role_is_fixed_role
, memberdatabaseprincipal.name AS member_name
, memberdatabaseprincipal.principal_id AS member_principal_id
, memberdatabaseprincipal.type AS member_type
, memberdatabaseprincipal.type_desc AS member_type_desc
, memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role
, memberserverprincipal.name AS member_principal_name
, memberserverprincipal.type_desc member_principal_type_desc
, N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement
FROM sys.database_principals AS roles
INNER JOIN sys.database_role_members
ON sys.database_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals AS memberdatabaseprincipal
ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id
LEFT OUTER JOIN sys.server_principals AS memberserverprincipal
ON memberserverprincipal.sid = memberdatabaseprincipal.sid
ORDER BY role_name
, member_name
44
CREATE PROCEDURE Get_permission
AS
DECLARE @db_name VARCHAR(200),
@sql_text VARCHAR(max)
SET @sql_text='Create table ##db_name (user_name varchar(max),'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text=@sql_text + @db_name + ' varchar(max),'
FETCH next FROM db_cursor INTO @db_name
END
CLOSE db_cursor
SET @sql_text=@sql_text + 'Server_perm varchar(max))'
EXEC (@sql_text)
DEALLOCATE db_cursor
DECLARE @RoleName VARCHAR(50)
DECLARE @UserName VARCHAR(50)
DECLARE @CMD VARCHAR(1000)
CREATE TABLE #permission
(
user_name VARCHAR(50),
databasename VARCHAR(50),
role VARCHAR(50)
)
DECLARE longspcur CURSOR FOR
SELECT name
FROM sys.server_principals
WHERE type IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND name NOT LIKE '##%'
AND name <> 'NT AUTHORITY\SYSTEM'
AND name <> 'ONDEMAND\Administrator'
AND name NOT LIKE 'steel%'
OPEN longspcur
FETCH next FROM longspcur INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(50),
role VARCHAR(50)
)
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(100),
membername VARCHAR(100),
membersid VARBINARY(2048)
)
SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + ''''
EXEC Sp_msforeachdb
@CMD
INSERT INTO #permission
SELECT @UserName 'user',
b.name,
u.role
FROM sys.sysdatabases b
LEFT OUTER JOIN #userroles_kk u
ON u.databasename = b.name --and u.Role='db_owner'
ORDER BY 1
DROP TABLE #userroles_kk;
DROP TABLE #rolemember_kk;
FETCH next FROM longspcur INTO @UserName
END
CLOSE longspcur
DEALLOCATE longspcur
TRUNCATE TABLE ##db_name
DECLARE @d1 VARCHAR(max),
@d2 VARCHAR(max),
@d3 VARCHAR(max),
@ss VARCHAR(max)
DECLARE perm_cur CURSOR FOR
SELECT *
FROM #permission
ORDER BY 2 DESC
OPEN perm_cur
FETCH next FROM perm_cur INTO @d1, @d2, @d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1
FROM ##db_name
WHERE user_name = @d1)
BEGIN
SET @ss='insert into ##db_name(user_name) values ('''
+ @d1 + ''')'
EXEC (@ss)
SET @ss='update ##db_name set ' + @d2 + '=''' + @d3
+ ''' where user_name=''' + @d1 + ''''
EXEC (@ss)
END
ELSE
BEGIN
DECLARE @var NVARCHAR(max),
@ParmDefinition NVARCHAR(max),
@var1 NVARCHAR(max)
SET @var = N'select @var1=' + @d2
+ ' from ##db_name where USER_NAME=''' + @d1
+ '''';
SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT';
EXECUTE Sp_executesql
@var,
@ParmDefinition,
@var1=@var1 output;
SET @var1=Isnull(@var1, ' ')
SET @var= ' update ##db_name set ' + @d2 + '=''' + @var1 + ' '
+ @d3 + ''' where user_name=''' + @d1 + ''' '
EXEC (@var)
END
FETCH next FROM perm_cur INTO @d1, @d2, @d3
END
CLOSE perm_cur
DEALLOCATE perm_cur
SELECT *
FROM ##db_name
DROP TABLE ##db_name
DROP TABLE #permission
11 回答
以下是Jeremy 2011年8月查询的完整版本,其中Brad(2011年10月)和iw.kuchin(2012年5月)建议的更改包括:
布拉德:为模式更正
[ObjectType]
和[ObjectName]
.iw.kuchin:对于
[ObjectType]
,最好只将obj.type_desc
用于OBJECT_OR_COLUMN
权限级别 . 对于所有其他情况,请使用perm.[class_desc]
.iw.kuchin:处理
IMPERSONATE
权限 .iw.kuchin:将
sys.login_token
替换为sys.server_principals
,因为它还会显示SQL登录,而不仅仅是Windows登录 .iw.kuchin:包括Windows组 .
iw.kuchin:排除用户
sys
和INFORMATION_SCHEMA .希望这可以节省其他人一两个小时的生命 .
:)
令人敬畏的剧本杰里米和贡献者!谢谢!
我有大量的用户,所以为所有用户运行这是一个噩梦 . 我无法添加评论,所以我发布了整个脚本的更改 . 我添加了一个变量where子句,因此我可以搜索用户名中最多匹配5个字符的任何内容(或留空的所有用户) . 没什么特别的,但我认为在一些用例中会有所帮助 .
遗憾的是,由于声誉不足,我无法对Sean Rose帖子发表评论,但是我不得不修改脚本的“公共”角色部分,因为它没有显示SCHEMA范围的权限,因为(INNER)JOIN对sys . 对象 . 之后更改为LEFT JOIN我还需要修改WHERE子句逻辑以省略系统对象 . 我对公共权限的修改查询如下 .
非常感谢精彩的审计脚本 .
我强烈建议审计用户使用真棒Kenneth Fisher(b | t)存储过程:
sp_DBPermissions
sp_SrvPermissions
无法评论已接受的答案,所以我会在这里添加一些评论:
我在架构问题上的第二个Brad . 从MS引用
sys.objects
表仅包含架构范围的对象 . 因此,要获取有关"higher level"对象的信息(例如我们的模式中的模式),您需要使用sys.schemas
表 .对于
[ObjectType]
,最好仅将obj.type_desc
用于OBJECT_OR_COLUMN
权限类 . 对于所有其他情况,请使用perm.[class_desc]
使用此查询处理不当的另一种类型的权限是
IMPERSONATE
. 要获取有关模仿的信息,请在perm.major_id = imp.principal_id
上使用sys.database_principals
进行LEFT JOIN
根据我的经验,最好将
sys.login_token
替换为sys.server_principals
,因为它还会显示SQL登录,而不仅仅是Windows登录应该将
'G'
添加到允许的主体类型以允许Windows组此外,可以从结果表中排除用户
sys
和INFORMATION_SCHEMA
,因为这些用户仅用于服务我将发布所有建议修复的第一段脚本,其他部分也应该更改:
根据Andomar的建议,这是我在查询中的第一次破解 . 此查询旨在提供用户直接应用于用户帐户的权限列表,或者通过用户拥有的角色 .
这是我的版本,改编自其他人 . 我刚刚花了30分钟试图记住我是如何想出这个的,而@Jeremy的答案似乎是核心灵感 . 我不想更新Jeremy的答案,以防我引入错误,所以我在这里发布我的版本 .
从SQL Server 2005开始,您可以使用系统视图 . 例如,此查询列出数据库中的所有用户及其权限:
请注意,用户也可以通过角色获得权限 . 例如,
db_data_reader
角色在大多数对象上授予select
权限 .上面的GetPermissions存储过程很好,但它使用Sp_msforeachdb,这意味着如果您的SQL实例具有包含空格或短划线和其他非最佳实践字符的任何数据库名称,它将中断 . 我创建了一个避免使用Sp_msforeachdb的版本,还包括两个指示1的列 - 如果Login是sysadmin登录(IsSysAdminLogin),还是2 - 如果登录是孤儿用户(IsEmptyRow) .
我看到的其他答案错过了数据库中可能的一些权限 . 下面代码中的第一个查询将获得不是系统对象的 everything 的数据库级权限 . 它也会生成相应的GRANT语句 . 第二个查询获得所有角色的关联 .
这必须是为每个数据库运行,但是太长而无法与sp_MSforeachdb一起使用 . 如果要这样做,则必须将其作为系统存储过程添加到master数据库 .
为了涵盖所有可能性,您还必须拥有一个检查服务器级别权限的脚本 .