LLI Wiki — My Digital Knowledge Base

SQL Server: script logins, roles and permissions to another instance

List logins, server roles and permissions, and generate the CREATE LOGIN, role and user statements to recreate them.

Scripts to list the logins, server roles and permissions of a SQL Server instance, and to generate the statements that recreate them on another instance or database.

List the members of a Windows group

xp_logininfo returns the members of a Windows group, or the groups of an account.

EXEC XP_LOGININFO 'DOMAIN\group', 'members'
EXEC XP_LOGININFO 'DOMAIN\user', 'all'
EXEC master..xp_logininfo 
@acctname = [DOMAIN\group],
@option = 'members'


EXEC master..xp_logininfo 
@acctname = [DOMAIN\user],
@option = 'members'

Test membership of a group or role

IS_MEMBER returns 1 if the current user is a member, 0 if not, and NULL if the group or role does not exist.

SELECT IS_MEMBER('group or role')

SELECT IS_MEMBER('db_owner')

List server permissions and roles per login

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

Generate the CREATE LOGIN statements

The query produces an IF SUSER_ID(...) IS NULL BEGIN CREATE LOGIN ... END; script for each login. For a SQL login, the script includes the password hash and the SID.

SELECT sp.sid, sl.sid,'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
               CASE 
                    WHEN SP.type_desc = 'SQL_LOGIN' 
						THEN 
							' WITH PASSWORD = ' + master.dbo.fn_varbintohexstr(SL.password_hash) + ' HASHED, '
							+'sid = '+ master.dbo.fn_varbintohexstr(sl.sid) +' ,'
							+'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')
        AND SP.name <> 'distributor_admin'

Generate the server role memberships

SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''';
' AS [-- Roles To Be Assigned --]
FROM master.sys.server_role_members SRM
INNER 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')
        AND SL.name <> 'distributor_admin';

Generate the database users

Run it in each database concerned.

SELECT 'USE '+ DB_NAME()+'; CREATE USER ['+dp.name+'] FOR LOGIN ['+dp.name+'];'+ 
        'ALTER USER ['+dp.name+'] WITH DEFAULT_SCHEMA=['+dp.default_schema_name+'];' AS [-- Logins To Be Created --]
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE (dp.type in ('S','G','U'))
        AND dp.name NOT LIKE '##%##'
        AND dp.name NOT LIKE 'NT AUTHORITY%'
        AND dp.name NOT LIKE 'NT SERVICE%'
        AND dp.name <> ('sa')
        AND dp.default_schema_name IS NOT NULL
        AND dp.name <> 'distributor_admin'
        AND dp.principal_id > 4

Generate the database permissions

Rows with dp.major_id = 0 are permissions at database level (not on an object).

SELECT 'USE '+ DB_NAME()+'; '+CASE WHEN dp.state <> 'W' THEN dp.state_desc ELSE 'GRANT' END +' ' + 
        dp.permission_name + ' TO ' + QUOTENAME(dpg.name) COLLATE database_default + 
        CASE WHEN dp.state <> 'W' THEN '' ELSE ' WITH GRANT OPTION' END +';' AS [-- Permission To Be Assign to the User --]
FROM    sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpg ON dp.grantee_principal_id = dpg.principal_id
WHERE   dp.major_id = 0 AND dpg.principal_id > 4
        AND (dpg.type in ('S','G','U'))
        AND dpg.name NOT LIKE '##%##'
        AND dpg.name NOT LIKE 'NT AUTHORITY%'
        AND dpg.name NOT LIKE 'NT SERVICE%'
        AND dpg.name <> ('sa')
        AND dpg.default_schema_name IS NOT NULL
        AND dpg.name <> 'distributor_admin'
        AND dpg.principal_id > 4
ORDER BY dpg.name

Notes

Mastodon LLIMastodon SocialFosstodon