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

enter image description here

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:

enter image description here

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
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]

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.