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
- Les scripts générés par
CREATE LOGINcontiennent des hash de mots de passe : les traiter comme des secrets et ne pas les diffuser. - Pour la synchronisation des logins et des jobs entre réplicas d'un groupe de disponibilité, voir l'article Sync SQL logins and jobs.