How to script out the SQL Server instance level permission of the logins

Question

are there any scripts to script out the SQL Server instance level permission of the logins?

Answer 1

I have this script of Greg Ryan if helps:

--Script All Logins / Users / and Roles

/****************************************************************
This Script Generates A script to Create all Logins, Server Roles
, DB Users and DB roles on a SQL Server

Greg Ryan

10/31/2013
****************************************************************/
SET NOCOUNT ON

DECLARE
        @sql nvarchar(max)
,       @Line int = 1
,       @max int = 0
,       @@CurDB nvarchar(100) = ''

CREATE TABLE #SQL
       (
        Idx int IDENTITY
       ,xSQL nvarchar(max)
       )

INSERT INTO #SQL
        ( xSQL
        )
        SELECT
                'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
                + QUOTENAME(name) + ''')
' + '   CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
                + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
                + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
                + QUOTENAME(COALESCE(default_database_name , 'master'))
                + ', DEFAULT_LANGUAGE='
                + QUOTENAME(COALESCE(default_language_name , 'us_english'))
                + ', CHECK_EXPIRATION=' + CASE is_expiration_checked
                                            WHEN 1 THEN 'ON'
                                            ELSE 'OFF'
                                          END + ', CHECK_POLICY='
                + CASE is_policy_checked
                    WHEN 1 THEN 'ON'
                    ELSE 'OFF'
                  END + '
Go

'
            FROM
                sys.sql_logins
            WHERE
                name <> 'sa'

INSERT INTO #SQL
        ( xSQL
        )
        SELECT
                'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
                + QUOTENAME(name) + ''')
' + '   CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
                + 'DEFAULT_DATABASE='
                + QUOTENAME(COALESCE(default_database_name , 'master'))
                + ', DEFAULT_LANGUAGE='
                + QUOTENAME(COALESCE(default_language_name , 'us_english'))
                + ';
Go

'
            FROM
                sys.server_principals
            WHERE
                type IN ( 'U' , 'G' )
                AND name NOT IN ( 'BUILTIN\Administrators' ,
                                  'NT AUTHORITY\SYSTEM' );

PRINT '/*****************************************************************************************/'
PRINT '/*************************************** Create Logins ***********************************/'
PRINT '/*****************************************************************************************/'
SELECT
        @Max = MAX(idx)
    FROM
        #SQL 
WHILE @Line <= @max
      BEGIN



            SELECT
                    @sql = xSql
                FROM
                    #SQL AS s
                WHERE
                    idx = @Line
            PRINT @sql

            SET @line = @line + 1

      END
DROP TABLE #SQL

CREATE TABLE #SQL2
       (
        Idx int IDENTITY
       ,xSQL nvarchar(max)
       )

INSERT INTO #SQL2
        ( xSQL
        )
        SELECT
                'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
                + QUOTENAME(R.name) + ';
GO

'
            FROM
                sys.server_principals L
            JOIN sys.server_role_members RM
            ON  L.principal_id = RM.member_principal_id
            JOIN sys.server_principals R
            ON  RM.role_principal_id = R.principal_id
            WHERE
                L.type IN ( 'U' , 'G' , 'S' )
                AND L.name NOT IN ( 'BUILTIN\Administrators' ,
                                    'NT AUTHORITY\SYSTEM' , 'sa' );


PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members     *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
        @Max = MAX(idx)
    FROM
        #SQL2 
SET @line = 1
WHILE @Line <= @max
      BEGIN



            SELECT
                    @sql = xSql
                FROM
                    #SQL2 AS s
                WHERE
                    idx = @Line
            PRINT @sql

            SET @line = @line + 1

      END
DROP TABLE #SQL2

PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'


--Drop Table #Db
CREATE TABLE #Db
       (
        idx int IDENTITY
       ,DBName nvarchar(100)
       );



INSERT INTO #Db
        SELECT
                name
            FROM
                master.dbo.sysdatabases
            WHERE
                name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
            ORDER BY
                name;


SELECT
        @Max = MAX(idx)
    FROM
        #Db
SET @line = 1
--Select * from #Db


--Exec sp_executesql @SQL

WHILE @line <= @Max
      BEGIN
            SELECT
                    @@CurDB = DBName
                FROM
                    #Db
                WHERE
                    idx = @line

            SET @SQL = 'Use ' + @@CurDB + '

Declare  @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR

Select  ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
                mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +

''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']''''; 
Go''  
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id


OPEN cur

FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN   
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END

CLOSE cur;
DEALLOCATE cur;';
--Print @SQL
Exec sp_executesql @SQL;
--Set @@Script = ''
            SET @Line = @Line + 1

      END

DROP TABLE #Db

Answer 2

Hi @sakuraime ,

Please refer to below which summarize the topic we are also giving the MS suggested script.

Server / Instance Level

• Script Logins with Passwords
• Script Login Server Roles
• Script the Server Level Permissions

You could refer the script for generating Login creation script for the given SQL Server instance. Here is the reference from the MS site.

There is another beautiful script provided by JP Chen and it also working for login migration.

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  

If above scripts are both not helpful, please refer below forums and check whether any of them is helpful:

SQL server level permissions
how to script out database users and permissions in all user databases in the instance
Scripting out database user level permissions

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.