LLI Wiki — My Digital Knowledge Base

SQL Server : scripter les logins, rôles et permissions vers une autre instance

Lister les logins, rôles serveur et permissions, et générer les CREATE LOGIN, rôles et utilisateurs à recréer.

Scripts pour lister les logins, les rôles serveur et les permissions d'une instance SQL Server, et générer les instructions qui les recréent sur une autre instance ou une autre base.

Lister les membres d'un groupe Windows

xp_logininfo renvoie les membres d'un groupe Windows, ou les groupes d'un compte.

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'

Tester l'appartenance à un groupe ou à un rôle

IS_MEMBER renvoie 1 si l'utilisateur courant est membre, 0 sinon, et NULL si le groupe ou le rôle n'existe pas.

SELECT IS_MEMBER('group or role')

SELECT IS_MEMBER('db_owner')

Lister les permissions et rôles serveur par 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

Générer les CREATE LOGIN

La requête produit un script IF SUSER_ID(...) IS NULL BEGIN CREATE LOGIN ... END; pour chaque login. Pour un login SQL, le script reprend le hash du mot de passe et le 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'

Générer les rôles serveur

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';

Générer les utilisateurs de base de données

À exécuter dans chaque base concernée.

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

Générer les permissions de base de données

Les lignes dp.major_id = 0 correspondent aux permissions au niveau de la base (et non d'un objet).

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

Remarques

Mastodon LLIMastodon SocialFosstodon