September 27, 2018 at 6:17 am
I have a script which creates a server role called dbareadonly. Here is part of it:USE master;
GO
CREATE SERVER ROLE dbareadonly AUTHORIZATION sa;
GO
GRANT CONNECT ANY DATABASE TO dbareadonly;
GO
GRANT CONNECT SQL TO dbareadonly;
GO
--Various other GRANTs
Assuming that the server role has been created, I would like to be able to write a SELECT statement which shows exactly what all of the GRANTs are for that role (this is an ongoing requirement, to convince auditors). It must be easy, but (being a simple developer) I could not find a way. Can anyone help, please?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 27, 2018 at 10:14 am
I would start with something simple like:
SELECT DISTINCT
P.state_desc COLLATE DATABASE_DEFAULT + ' '
+ P.[permission_name] COLLATE DATABASE_DEFAULT + ' TO '
+ R.[name] COLLATE DATABASE_DEFAULT + ';'
FROM sys.server_principals R
JOIN sys.server_permissions P
ON R.principal_id = P.grantee_principal_id
WHERE R.type_desc = 'SERVER_ROLE'
AND R.[name] = 'dbareadonly';
September 27, 2018 at 10:49 am
Ken McKelvey - Thursday, September 27, 2018 10:14 AMI would start with something simple like:
SELECT DISTINCT
P.state_desc COLLATE DATABASE_DEFAULT + ' '
+ P.[permission_name] COLLATE DATABASE_DEFAULT + ' TO '
+ R.[name] COLLATE DATABASE_DEFAULT + ';'
FROM sys.server_principals R
JOIN sys.server_permissions P
ON R.principal_id = P.grantee_principal_id
WHERE R.type_desc = 'SERVER_ROLE'
AND R.[name] = 'dbareadonly';
That is most helpful, thank you. With just a few minor tweaks, this gave me what I need.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 1, 2018 at 4:27 am
Phil Parkin - Thursday, September 27, 2018 6:17 AMI have a script which creates a server role called dbareadonly. Here is part of it:USE master;
GOCREATE SERVER ROLE dbareadonly AUTHORIZATION sa;
GOGRANT CONNECT ANY DATABASE TO dbareadonly;
GOGRANT CONNECT SQL TO dbareadonly;
GO--Various other GRANTs
Assuming that the server role has been created, I would like to be able to write a SELECT statement which shows exactly what all of the GRANTs are for that role (this is an ongoing requirement, to convince auditors). It must be easy, but (being a simple developer) I could not find a way. Can anyone help, please?
Try this
[/*
Script Date 19th February 2010
Script Author Perry Whittle
Script Description This script returns all server logins, any Server Roles they hold
and server level permissions assigned*/
SELECT sp.name AS ServerPrincipal,
sp.type_desc AS LoginType,
CASE sp.is_disabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS UserDisabled,
sp.create_date AS DateCreated,
sp.modify_date AS DateModified,
sp.default_database_name AS DefaultDB,
sp.default_language_name AS DefaultLang,
ISNULL(STUFF((SELECT ',' +
CASE ssp22.name
WHEN 'sysadmin' THEN ssp22.name + ' "Danger Will Robinson"'
ELSE ssp22.name
END
FROM sys.server_principals ssp2
INNER JOIN sys.server_role_members ssrm2
ON ssp2.principal_id = ssrm2.member_principal_id
INNER JOIN sys.server_principals ssp22
ON ssrm2.role_principal_id = ssp22.principal_id
WHERE ssp2.principal_id = sp.principal_id
ORDER BY ssp2.name
FOR XML PATH(N''), TYPE).value(N'.[1]',
N'nvarchar(max)'), 1, 1, N''), 'NoRolesHeld') AS ListofServerRoles,
ISNULL(STUFF((SELECT ';' + ' Permission [' + sspm3.permission_name + '] is [' +
CASE
WHEN sspm3.state_desc = 'GRANT' THEN 'Granted]'
WHEN sspm3.state_desc = 'DENY' THEN 'Denied]'
END AS PermGrants
FROM sys.server_principals ssp3
INNER JOIN sys.server_permissions sspm3
ON ssp3.principal_id = sspm3.grantee_principal_id
WHERE sspm3.class = 100 AND
sspm3.grantee_principal_id = sp.principal_id
FOR XML PATH(N''), TYPE).value(N'.[1]',
N'nvarchar(max)'), 1, 1, N''), 'NoServerPermissions')
+ ' in Server::' + @@ServerName + '' AS PermGrants
FROM sys.server_principals sp
WHERE sp.type IN ('S','G','U') AND sp.name NOT LIKE '##%##'
ORDER BY ServerPrincipal
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 1, 2018 at 5:58 am
Perry Whittle - Monday, October 1, 2018 4:27 AMTry this[
/*
Script Date 19th February 2010
Script Author Perry Whittle
Script Description This script returns all server logins, any Server Roles they hold
and server level permissions assigned*/
SELECT sp.name AS ServerPrincipal,
sp.type_desc AS LoginType,
CASE sp.is_disabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS UserDisabled,
sp.create_date AS DateCreated,
sp.modify_date AS DateModified,
sp.default_database_name AS DefaultDB,
sp.default_language_name AS DefaultLang,
ISNULL(STUFF((SELECT ',' +
CASE ssp22.name
WHEN 'sysadmin' THEN ssp22.name + ' "Danger Will Robinson"'
ELSE ssp22.name
END
FROM sys.server_principals ssp2
INNER JOIN sys.server_role_members ssrm2
ON ssp2.principal_id = ssrm2.member_principal_id
INNER JOIN sys.server_principals ssp22
ON ssrm2.role_principal_id = ssp22.principal_id
WHERE ssp2.principal_id = sp.principal_id
ORDER BY ssp2.name
FOR XML PATH(N''), TYPE).value(N'.[1]',
N'nvarchar(max)'), 1, 1, N''), 'NoRolesHeld') AS ListofServerRoles,
ISNULL(STUFF((SELECT ';' + ' Permission [' + sspm3.permission_name + '] is [' +
CASE
WHEN sspm3.state_desc = 'GRANT' THEN 'Granted]'
WHEN sspm3.state_desc = 'DENY' THEN 'Denied]'
END AS PermGrants
FROM sys.server_principals ssp3
INNER JOIN sys.server_permissions sspm3
ON ssp3.principal_id = sspm3.grantee_principal_id
WHERE sspm3.class = 100 AND
sspm3.grantee_principal_id = sp.principal_id
FOR XML PATH(N''), TYPE).value(N'.[1]',
N'nvarchar(max)'), 1, 1, N''), 'NoServerPermissions')
+ ' in Server::' + @@ServerName + '' AS PermGrants
FROM sys.server_principals sp
WHERE sp.type IN ('S','G','U') AND sp.name NOT LIKE '##%##'
ORDER BY ServerPrincipal
Danger Will Robinson indeed!
Nice script, thank you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply