How to script out the grant view on login permission?

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

I create the following login without any permissions.

USE [master]
GO

 IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
 CREATE LOGIN [Radhe] WITH PASSWORD=N'HareKrishna108, 
 DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 GO

when I execute the following query as that specific login I get the following results:

execute as login='Radhe'

select * from sys.syslogins

Now I grant some permissions to [Radhe] so that she can have a look at some of my existing logins on that same server:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

Now when I run the following code:

execute as login='Radhe'

select * from sys.syslogins

I get to see the logins I have granted [Radhe] the relevant permissions:

I need to create this very same login and grant these very same permissions on several servers.

How can I script these permissions that I have granted above?

I have found the following script on this nice site, but it does not seem to be working for the above listed permissions in particular:

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
 CASE 
 WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
 + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
 ELSE ' FROM WINDOWS WITH'
 END 
 +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
 ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
 AND SL.name NOT LIKE '##%##'
 AND SL.name NOT LIKE 'NT AUTHORITY%'
 AND SL.name NOT LIKE 'NT SERVICE%'
 AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

SET NOCOUNT OFF

sql-server sql-server-2016 permissions scripting logins

share|improve this question

asked 2 hours ago

marcello miorelli

5,1691659122

add a comment | 

up vote
2
down vote

favorite

I create the following login without any permissions.

USE [master]
GO

 IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
 CREATE LOGIN [Radhe] WITH PASSWORD=N'HareKrishna108, 
 DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 GO

when I execute the following query as that specific login I get the following results:

execute as login='Radhe'

select * from sys.syslogins

Now I grant some permissions to [Radhe] so that she can have a look at some of my existing logins on that same server:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

Now when I run the following code:

execute as login='Radhe'

select * from sys.syslogins

I get to see the logins I have granted [Radhe] the relevant permissions:

I need to create this very same login and grant these very same permissions on several servers.

How can I script these permissions that I have granted above?

I have found the following script on this nice site, but it does not seem to be working for the above listed permissions in particular:

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
 CASE 
 WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
 + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
 ELSE ' FROM WINDOWS WITH'
 END 
 +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
 ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
 AND SL.name NOT LIKE '##%##'
 AND SL.name NOT LIKE 'NT AUTHORITY%'
 AND SL.name NOT LIKE 'NT SERVICE%'
 AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

SET NOCOUNT OFF

sql-server sql-server-2016 permissions scripting logins

share|improve this question

asked 2 hours ago

marcello miorelli

5,1691659122

add a comment | 

up vote
2
down vote

favorite

up vote
2
down vote

favorite

I create the following login without any permissions.

USE [master]
GO

 IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
 CREATE LOGIN [Radhe] WITH PASSWORD=N'HareKrishna108, 
 DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 GO

when I execute the following query as that specific login I get the following results:

execute as login='Radhe'

select * from sys.syslogins

Now I grant some permissions to [Radhe] so that she can have a look at some of my existing logins on that same server:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

Now when I run the following code:

execute as login='Radhe'

select * from sys.syslogins

I get to see the logins I have granted [Radhe] the relevant permissions:

I need to create this very same login and grant these very same permissions on several servers.

How can I script these permissions that I have granted above?

I have found the following script on this nice site, but it does not seem to be working for the above listed permissions in particular:

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
 CASE 
 WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
 + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
 ELSE ' FROM WINDOWS WITH'
 END 
 +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
 ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
 AND SL.name NOT LIKE '##%##'
 AND SL.name NOT LIKE 'NT AUTHORITY%'
 AND SL.name NOT LIKE 'NT SERVICE%'
 AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

SET NOCOUNT OFF

sql-server sql-server-2016 permissions scripting logins

share|improve this question

asked 2 hours ago

marcello miorelli

5,1691659122

I create the following login without any permissions.

USE [master]
GO

 IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
 CREATE LOGIN [Radhe] WITH PASSWORD=N'HareKrishna108, 
 DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 GO

when I execute the following query as that specific login I get the following results:

execute as login='Radhe'

select * from sys.syslogins

Now I grant some permissions to [Radhe] so that she can have a look at some of my existing logins on that same server:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

Now when I run the following code:

execute as login='Radhe'

select * from sys.syslogins

I get to see the logins I have granted [Radhe] the relevant permissions:

I need to create this very same login and grant these very same permissions on several servers.

How can I script these permissions that I have granted above?

I have found the following script on this nice site, but it does not seem to be working for the above listed permissions in particular:

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
 CASE 
 WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
 + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
 ELSE ' FROM WINDOWS WITH'
 END 
 +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
 ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
 AND SL.name NOT LIKE '##%##'
 AND SL.name NOT LIKE 'NT AUTHORITY%'
 AND SL.name NOT LIKE 'NT SERVICE%'
 AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

SET NOCOUNT OFF

sql-server sql-server-2016 permissions scripting logins

sql-server sql-server-2016 permissions scripting logins

share|improve this question

asked 2 hours ago

marcello miorelli

5,1691659122

share|improve this question

asked 2 hours ago

marcello miorelli

5,1691659122

share|improve this question

share|improve this question

asked 2 hours ago

marcello miorelli

5,1691659122

asked 2 hours ago

marcello miorelli

5,1691659122

asked 2 hours ago

marcello miorelli

5,1691659122

5,1691659122

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
3
down vote



accepted

The server permission for granting rights against logins has a class_desc of SERVER_PRINCIPAL. So in that case you can include the ON LOGIN:: bits and join (again) against sys.server_principals. Also tell radhe and others to please use that view instead of sys.syslogins, which was deprecated 13 years ago now...

SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name 
 + CASE SrvPerm.class_desc WHEN 'SERVER_PRINCIPAL' THEN 
 ' ON LOGIN::' + QUOTENAME(t.name) ELSE '' END
 + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 INNER JOIN sys.server_principals AS SP 
 ON SrvPerm.grantee_principal_id = SP.principal_id 
 LEFT OUTER JOIN sys.server_principals AS t
 ON SrvPerm.major_id = t.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

Results:

GRANT CONNECT SQL TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦

145k19276465

• 
  
  
  

  
  

  
  
  
  
  
  

  
  +1 wonderful script... also hopefully you come back to England again next year!
  – marcello miorelli
  2 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218272%2fhow-to-script-out-the-grant-view-on-login-permission%23new-answer', 'question_page');

);

Post as a guest

Name Email

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
3
down vote



accepted

The server permission for granting rights against logins has a class_desc of SERVER_PRINCIPAL. So in that case you can include the ON LOGIN:: bits and join (again) against sys.server_principals. Also tell radhe and others to please use that view instead of sys.syslogins, which was deprecated 13 years ago now...

SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name 
 + CASE SrvPerm.class_desc WHEN 'SERVER_PRINCIPAL' THEN 
 ' ON LOGIN::' + QUOTENAME(t.name) ELSE '' END
 + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 INNER JOIN sys.server_principals AS SP 
 ON SrvPerm.grantee_principal_id = SP.principal_id 
 LEFT OUTER JOIN sys.server_principals AS t
 ON SrvPerm.major_id = t.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

Results:

GRANT CONNECT SQL TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦

145k19276465

• 
  
  
  

  
  

  
  
  
  
  
  

  
  +1 wonderful script... also hopefully you come back to England again next year!
  – marcello miorelli
  2 hours ago
  

  
  
  
  

add a comment | 

up vote
3
down vote



accepted

The server permission for granting rights against logins has a class_desc of SERVER_PRINCIPAL. So in that case you can include the ON LOGIN:: bits and join (again) against sys.server_principals. Also tell radhe and others to please use that view instead of sys.syslogins, which was deprecated 13 years ago now...

SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name 
 + CASE SrvPerm.class_desc WHEN 'SERVER_PRINCIPAL' THEN 
 ' ON LOGIN::' + QUOTENAME(t.name) ELSE '' END
 + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 INNER JOIN sys.server_principals AS SP 
 ON SrvPerm.grantee_principal_id = SP.principal_id 
 LEFT OUTER JOIN sys.server_principals AS t
 ON SrvPerm.major_id = t.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

Results:

GRANT CONNECT SQL TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦

145k19276465

• 
  
  
  

  
  

  
  
  
  
  
  

  
  +1 wonderful script... also hopefully you come back to England again next year!
  – marcello miorelli
  2 hours ago
  

  
  
  
  

add a comment | 

up vote
3
down vote



accepted

up vote
3
down vote



accepted

The server permission for granting rights against logins has a class_desc of SERVER_PRINCIPAL. So in that case you can include the ON LOGIN:: bits and join (again) against sys.server_principals. Also tell radhe and others to please use that view instead of sys.syslogins, which was deprecated 13 years ago now...

SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name 
 + CASE SrvPerm.class_desc WHEN 'SERVER_PRINCIPAL' THEN 
 ' ON LOGIN::' + QUOTENAME(t.name) ELSE '' END
 + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 INNER JOIN sys.server_principals AS SP 
 ON SrvPerm.grantee_principal_id = SP.principal_id 
 LEFT OUTER JOIN sys.server_principals AS t
 ON SrvPerm.major_id = t.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

Results:

GRANT CONNECT SQL TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦

145k19276465

The server permission for granting rights against logins has a class_desc of SERVER_PRINCIPAL. So in that case you can include the ON LOGIN:: bits and join (again) against sys.server_principals. Also tell radhe and others to please use that view instead of sys.syslogins, which was deprecated 13 years ago now...

SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN SrvPerm.state_desc 
 ELSE 'GRANT' 
 END
 + ' ' + SrvPerm.permission_name 
 + CASE SrvPerm.class_desc WHEN 'SERVER_PRINCIPAL' THEN 
 ' ON LOGIN::' + QUOTENAME(t.name) ELSE '' END
 + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
 THEN '' 
 ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 INNER JOIN sys.server_principals AS SP 
 ON SrvPerm.grantee_principal_id = SP.principal_id 
 LEFT OUTER JOIN sys.server_principals AS t
 ON SrvPerm.major_id = t.principal_id
WHERE SP.type IN ( 'S', 'U', 'G' ) 
 AND SP.name NOT LIKE '##%##'
 AND SP.name NOT LIKE 'NT AUTHORITY%'
 AND SP.name NOT LIKE 'NT SERVICE%'
 AND SP.name <> ('sa');

Results:

GRANT CONNECT SQL TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦

145k19276465

share|improve this answer

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦

145k19276465

answered 2 hours ago

Aaron Bertrand♦

145k19276465

answered 2 hours ago

Aaron Bertrand♦

145k19276465

145k19276465

• 
  
  
  

  
  

  
  
  
  
  
  

  
  +1 wonderful script... also hopefully you come back to England again next year!
  – marcello miorelli
  2 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  +1 wonderful script... also hopefully you come back to England again next year!
  – marcello miorelli
  2 hours ago
  

  
  
  
  

+1 wonderful script... also hopefully you come back to England again next year!
– marcello miorelli
2 hours ago

+1 wonderful script... also hopefully you come back to England again next year!
– marcello miorelli
2 hours ago

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218272%2fhow-to-script-out-the-grant-view-on-login-permission%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email