question

sakuraime avatar image
0 Votes"
sakuraime asked MelissaMa-msft commented

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

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

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sakuraime,

Could you please validate both answers and provide any update?

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
StratosMatzouranis avatar image
0 Votes"
StratosMatzouranis answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.