Extraction des droits SQL
Nombreuses sont les fois où le besoin de pouvoir extraire l’ensemble des droits d’une instance est nécessaire pour répondre à différents besoins, comme par exemple :
- Migration et donc déplacement des comptes,
- Sauvegarde des droits (autrement que par backup de master et des autres bases),
- Création d’un nouveau compte SQL depuis un compte déjà existant,
- La réplication ne pouvant pas propager les droits, il est nécessaire de combler ce manque,
- Faire un audit de la sécurité du serveur.
Voici la liste des DMV sur lequel le modèle relationnel de la partie sécurité reposent :
- sys.server_principals
- sys.sql_logins
- sys.server_role_members
- sys.server_permissions
- sys.database_principals
- sys.database_role_members
- sys.database_permissions
Autour de ces DMV gravitent d’autres DMV (exemple : sys.objects).
Le script suivant permet d’extraire :
- La configuration des logins,
- Les rôles serveurs auxquels sont associés les logins,
- Les permissions serveurs auxquels sont associés les logins,
- La configuration des users,
- Les rôles de base de données auxquels sont associés les users,
- Les permissions users auxquels sont associés les users.
A
*Attention, cette version du script ne gère pas les contained user (nouveauté SQL Server 2012), ce type de user remonte actuellement comme orphelin.
*
Prérequis : Installation du kb-918992.
USE master
GO
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.procedures WHERE name = 'ps_extract_rights')
BEGIN
EXEC ('CREATE PROCEDURE ps_extract_rights
AS
BEGIN
SELECT ''NOT IMPLEMENTED''
END'
)
END
GO
-- =======================================================================================================================================
-- Author: Grégory NAIL
-- Description: Ce script permet de copier les droits d'un login pour créer un nouveau login et lui donner les mêmes droits
-- Ce script permet d'extraire les droits des logins de l'instance et des users (des bases online)
-- NOTE : CETTE PROCEDURE FAIT SEULEMENT DES SELECT, IL FAUT EXÉCUTER LE RÉSULTAT DE LA REQUÊTE
-- Parametres :
-- @AuditRight : Indique si on veut extraire un dataset détaillé des droits
-- @ExtractScript : Indique si on souhaite avoir les scripts de sortie de création des login/user avec les droits associés
-- @LoginSource : si valorisé, on souhaite avoir seulement les droits d'un login spécifique et des users associés
-- @LoginDest : si valorisé, on souhaite avoir une copie du login source pour une éventuelle recréation
-- @AddRight : ajoute des droits à un login et aux users associés (utile pour un login existant qui voit son périmètre d'action augmenté)
-- Si @AddRight = 0, il peut y avoir un drop login et des drop user si existant, afin de récréer le login/user depuis 0
-- Si @AddRight = 1, il y a un create login et user en cas de non existence, aucun droit n'est supprimé
-- (/!\ attention aux possibles pertes de droit à cause d'un DENY)
-- @WithEvenPWD : Donne le meme mot de passe au nouveau login par rapport au login de copie
-- Si @WithEvenPWD = 1, copie du MdP, nécessite KB 918992 - http://support.microsoft.com/kb/918992/fr
-- Si @WithEvenPWD = 0, donne un mot de passe par défaut avec l'option MUST_CHANGE dans le cas ou @newPWD n'est pas valorisé
-- @newPWD : Password souhaité pour un nouveau login (7 caractères minimum).
-- =======================================================================================================================================
--EXEC master..ps_extract_rights @AuditRight = 1, @ExtractScript = 1, @LoginSource = 'nallignet', @LoginDest = 'LoginTemoin2', @AddRight = 0, @WithEvenPWD = 1
ALTER PROCEDURE ps_extract_rights
@AuditRight BIT
, @ExtractScript BIT
, @LoginSource SYSNAME = NULL
, @LoginDest SYSNAME = NULL
, @AddRight BIT = NULL
, @WithEvenPWD BIT = NULL
, @newPWD VARCHAR(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON
--Construction d'un mot de passe par défaut dans le cas ou il y a une extraction du script de création de login pour un copié-collé vers un autre login
DECLARE @DefaultPWD BIT
SET @DefaultPWD = 0
--On valorise un mot de passe par défaut dans le cas ou :
--Pas de mot de passe en paramètre
--Pas de souhait d'avoir le même mot de passe que le login source
--Un nouveau login précisé
--Souhait d'avoir le script de création de login
IF (@newPWD IS NULL OR @newPWD = '') AND @WithEvenPWD = 0 AND (@LoginDest IS NOT NULL) AND @ExtractScript = 1
SET @DefaultPWD = 1
IF @DefaultPWD = 1
SET @newPWD = 'RobustPWD;)'
/*********************************************************************************/
/******************Quelques vérifications pour le fonctionnement******************/
/*********************************************************************************/
DECLARE @is_error BIT
SET @is_error = 0
--Quand il n'y a pas d'extraction de script, il ne faut pas valoriser @LoginDest, @AddRight, @WithEvenPWD & @newPWD
IF @ExtractScript = 0 AND (@LoginDest IS NOT NULL OR @AddRight IS NOT NULL OR @WithEvenPWD IS NOT NULL OR @newPWD IS NOT NULL)
BEGIN
RAISERROR ('Quand il n''y a pas d''extraction de script, il ne faut pas valoriser @LoginDest, @AddRight, @WithEvenPWD & @newPWD', 16, 1)
SET @is_error = 1
END
--Quand on ne veut pas étudier un login particulier mais l'ensemble, il ne faut pas paramétrer @LoginDest, @AddRight, @WithEvenPWD & @newPWD
IF @LoginSource IS NULL AND (@LoginDest IS NOT NULL OR @AddRight IS NOT NULL OR @WithEvenPWD IS NOT NULL OR @newPWD IS NOT NULL)
BEGIN
RAISERROR ('Quand on ne veut pas étudier un login particulier mais l''ensemble, il ne faut pas paramétrer @LoginDest, @AddRight, @WithEvenPWD & @newPWD', 16, 1)
SET @is_error = 1
END
--Quand on veut juste étudier un login particulier sans les copier vers une autre login, il n'est pas nécessaire de valoriser @WithEvenPWD & @newPWD
IF (@LoginSource IS NOT NULL) AND @LoginDest IS NULL AND (@WithEvenPWD IS NOT NULL OR @newPWD IS NOT NULL)
BEGIN
RAISERROR ('Quand on veut juste étudier un login particulier sans les copier vers une autre login, il n''est pas nécessaire de valoriser @AddRight & @WithEvenPWD & @newPWD', 16, 1)
SET @is_error = 1
END
--quand @LoginDest est valorisé, @LoginSource doit l'être aussi
IF (@LoginSource = '' OR @LoginSource IS NULL) AND (@LoginDest IS NOT NULL)
BEGIN
RAISERROR ('Il faut valoriser le paramètre @LoginSource quand @LoginDest est valorisé', 16, 1)
SET @is_error = 1
END
--Incohérence de configuration du loginDest avec AddRight & WithEvenMdP
IF (@LoginDest IS NOT NULL) AND (@AddRight IS NULL OR @WithEvenPWD IS NULL)
BEGIN
RAISERROR ('On ne peut pas avoir @LoginDest valorisé et les paramètres @AddRight et/ou @WithEvenPWD non valorisés', 16, 1)
SET @is_error = 1
END
--Longueur minimum de 7 caractère du mots de passe
IF @newPWD IS NOT NULL AND LEN(@newPWD) < 7
BEGIN
RAISERROR ('Le mot de passe doit avoir une longueur minimum de 7 caractères', 16, 1)
END
--Vérication d'incohérence de configuration de mot de passe
IF (@newPWD IS NOT NULL) AND @WithEvenPWD = 1 AND (@LoginDest IS NOT NULL)
BEGIN
RAISERROR ('On ne peut pas avoir @WithEvenPWD à 1 et @LoginDest valorisés et configurer le paramètre @newPWD', 16, 1)
SET @is_error = 1
END
--Vérification de l'existence du login
IF (@LoginSource IS NOT NULL) AND NOT EXISTS (SELECT TOP 1 1 FROM sys.server_principals WHERE name = @LoginSource AND Type IN ('S','U','G'))
BEGIN
DECLARE @MsgErreur_LoginNotExists NVARCHAR(MAX)
SET @MsgErreur_LoginNotExists = 'Le login : ' + @LoginSource + ' n''existe pas sur cette instance'
RAISERROR (@MsgErreur_LoginNotExists, 16, 1)
SET @is_error = 1
END
--Vérificaion de l'existence du kb 918992
IF OBJECT_ID ('sp_hexadecimal') IS NULL AND @WithEvenPWD = 1
BEGIN
RAISERROR ('Le KB 918992 est indispensable lorsqu''il est souhaité d''avoir le meme mot de passe pour le nouveau login (paramètre : @WithEvenPWD = 1
Lien d''installation du KB 918992 : http://support.microsoft.com/kb/918992/fr', 16, 1)
SET @is_error = 1
END
--En cas de la présence d'erreur, le déroulement de l'algo suivant ne peut pas être assuré
IF @is_error = 1
GOTO Finish
/*********************************************************************************/
/***********************Début de l'algo de récup des droits***********************/
/*********************************************************************************/
--1 récupérération configuration Login + association aux roles serveur
IF OBJECT_ID('tempdb..#ServerRole_Login') IS NOT NULL
DROP TABLE #ServerRole_Login
SELECT sp.sid
, sp.name AS [LoginName]
, sp.type_desc AS [LoginType]
, ISNULL(r.name,'public') AS [ServerRole]
, sp.is_disabled
, sp.default_database_name
, sp.default_language_name
, CASE WHEN l.is_policy_checked = 1 THEN 'ON' ELSE CASE WHEN l.is_policy_checked IS NULL THEN NULL ELSE 'OFF' END END AS policy_checked
, CASE WHEN l.is_expiration_checked = 1 THEN 'ON' ELSE CASE WHEN l.is_expiration_checked IS NULL THEN NULL ELSE 'OFF' END END AS expiration_checked
INTO #ServerRole_Login
FROM sys.server_principals AS sp
LEFT JOIN sys.sql_logins AS l
ON l.principal_id = sp.principal_id
LEFT JOIN (
SELECT srm.member_principal_id, spr.name
FROM sys.server_role_members AS srm
RIGHT JOIN sys.server_principals AS spr
ON spr.principal_id = srm.role_principal_id
AND spr.type = 'R'
) r ON r.member_principal_id = sp.principal_id
WHERE (sp.name = @LoginSource OR @LoginSource IS NULL)
AND sp.Type IN ('S','U','G')
--2 récupérération configuration des permissions serveur des logins
IF OBJECT_ID('tempdb..#ServerPermission_Login') IS NOT NULL
DROP TABLE #ServerPermission_Login
SELECT sp.name AS [LoginName]
, sp.type_desc AS [LoginType]
, perm.class_desc
, perm.type AS [TypePermission]
, perm.permission_name AS [ServerPermission]
, CASE WHEN perm.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE perm.state_desc END [State_desc]
, CASE WHEN perm.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 1 ELSE 0 END AS isGrantOption
, perm.name AS [Grantor]
, sp.is_disabled
, CASE perm.class_desc
WHEN 'SERVER_PRINCIPAL' THEN p.name
WHEN 'ENDPOINT' THEN e.name END
AS Major
INTO #ServerPermission_Login
FROM sys.server_principals AS sp
INNER JOIN (
SELECT perm.grantee_principal_id, perm.type, perm.permission_name, grantor.name, perm.state_desc, perm.major_id, perm.class_desc
FROM sys.server_permissions AS perm
RIGHT JOIN sys.server_principals AS grantor
ON grantor.principal_id = perm.grantor_principal_id
) perm ON perm.grantee_principal_id = sp.principal_id
LEFT JOIN sys.server_principals p
ON p.principal_id = perm.major_id
AND perm.class_desc = 'SERVER_PRINCIPAL'
LEFT JOIN sys.endpoints e
ON e.endpoint_id = perm.major_id
AND perm.class_desc = 'ENDPOINT'
WHERE (sp.name = @LoginSource OR @LoginSource IS NULL)
AND sp.Type IN ('S','U','G')
IF OBJECT_ID('tempdb..#LoginCredential') IS NOT NULL
DROP TABLE #LoginCredential
SELECT sp.name AS Login_name
, c.name AS Credential_name
INTO #LoginCredential
FROM sys.server_principals sp
INNER JOIN sys.server_principal_credentials pc
ON pc.principal_id = sp.principal_id
INNER JOIN sys.credentials c
ON c.credential_id = pc.credential_id
WHERE (sp.name = @LoginSource OR @LoginSource IS NULL)
AND sp.Type IN ('S','U','G')
--Table tempo qui contiendra : le mapping login user
IF OBJECT_ID('tempdb..#Login_User') IS NOT NULL
DROP TABLE #Login_User
CREATE TABLE #Login_User
(
DataBaseName SYSNAME
, UserName SYSNAME
, LoginName SYSNAME
, Default_Schema_Name SYSNAME NULL
)
--Table tempo qui contiendra : les user orphelins aillant le même nom que le login source quand précisé ou autrement tous les user orphelins
IF OBJECT_ID('tempdb..#User_Orphelins') IS NOT NULL
DROP TABLE #User_Orphelins
CREATE TABLE #User_Orphelins
(
DataBaseName SYSNAME
, UserName SYSNAME
, LoginName SYSNAME NULL
, Default_Schema_Name SYSNAME NULL
)
--Table tempo qui contiendra : l'association des roles de base de donnée pour des users
IF OBJECT_ID('tempdb..#DataBaseRole_User') IS NOT NULL
DROP TABLE #DataBaseRole_User
CREATE TABLE #DataBaseRole_User
(
DataBaseName SYSNAME
, UserName SYSNAME
, LoginType NVARCHAR(120)
, DataBaseRole SYSNAME
)
--Table tempo qui contiendra : les permissions de base de données pour des users
IF OBJECT_ID('tempdb..#DataBasePermission_User') IS NOT NULL
DROP TABLE #DataBasePermission_User
CREATE TABLE #DataBasePermission_User
(
DataBaseName SYSNAME
, class_desc NVARCHAR(120)
, UserName SYSNAME
, LoginType NVARCHAR(120)
, TypePermission CHAR(4)
, DBPermission NVARCHAR(256)
, State NVARCHAR(120)
, isGrantOption INT
, Grantor SYSNAME
, ObjectName NVARCHAR(514) NULL
, ColumnName SYSNAME NULL
)
--Boucle sur les bases pour récupérer l'ensemble des informations sur les users
DECLARE ListDB CURSOR FOR
SELECT Name
FROM sys.databases db
WHERE state_desc = 'ONLINE'
AND source_database_id IS NULL --on ne prend pas les bases snapshots
DECLARE @ReqBase VARCHAR(MAX)
DECLARE @Name SYSNAME
OPEN ListDB
FETCH NEXT FROM ListDB
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ReqBase = '
USE [' + @Name + ']
INSERT INTO #Login_User
SELECT ''' + @Name + ''', dp.name, sp.name, default_schema_name
FROM sys.database_principals dp
INNER JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE sp.Type IN (''S'',''U'',''G'')
' + CASE WHEN @LoginSource IS NULL THEN '' ELSE 'AND sp.name = ''' + @LoginSource + '''' END
SET @ReqBase = @ReqBase +
'
INSERT INTO #User_Orphelins
SELECT ''' + @Name + ''', dp.name, NULL, default_schema_name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE sp.name IS NULL
AND dp.Type IN (''S'',''U'',''G'')
AND dp.name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'')
' + CASE WHEN @LoginSource IS NULL THEN '' ELSE 'AND dp.name = ''' + @LoginSource + '''' END
SET @ReqBase = @ReqBase +
'
INSERT INTO #DataBaseRole_User
SELECT ''' + @Name + '''
, dp.name AS [UserName]
, dp.type_desc AS [LoginType]
, dpr.name AS [DataBaseRole]
FROM sys.database_role_members AS r
INNER JOIN sys.database_principals AS dp
ON dp.principal_id = r.member_principal_id
INNER JOIN sys.database_principals AS dpr
ON dpr.principal_id = r.role_principal_id
AND dpr.type = ''R''
INNER JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE sp.Type IN (''S'',''U'',''G'')
' + CASE WHEN @LoginSource IS NULL THEN '' ELSE 'AND sp.name = ''' + @LoginSource + '''' END
SET @ReqBase = @ReqBase +
'
INSERT INTO #DataBasePermission_User
SELECT ''' + @Name + '''
, perm.class_desc
, dp.name AS [UserName]
, sp.type_desc AS [LoginType]
, perm.type AS [TypePermission]
, perm.permission_name AS [DBPermission]
, CASE WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE perm.state_desc END [State]
, CASE WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION'' THEN 1 ELSE 0 END AS isGrantOption
, grantor.name AS [Grantor]
--, perm.major_id, perm.minor_id
, CASE class_desc
WHEN ''OBJECT_OR_COLUMN'' THEN COALESCE(''['' + oc.sch + ''].['' + oc.obj + '']'', ''['' + soc.sch + ''].['' + soc.obj + '']'', ''['' + o.sch + ''].['' + o.obj + '']'', ''['' + so.sch + ''].['' + so.obj + '']'')
WHEN ''TYPE'' THEN ''['' + t.sch + ''].'' + ''['' + t.typ + '']''
WHEN ''DATABASE_PRINCIPAL'' THEN ''['' + u.name + '']''
WHEN ''SCHEMA'' THEN ''['' + sch.name + '']''
WHEN ''ASSEMBLY'' THEN ''['' + ass.name + '']''
WHEN ''FULLTEXT_CATALOG'' THEN ''['' + ftxt.name + '']''
WHEN ''MESSAGE_TYPE'' THEN ''['' + brk_smt.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
WHEN ''SERVICE_CONTRACT'' THEN ''['' + brk_sc.name + '']''
WHEN ''REMOTE_SERVICE_BINDING'' THEN ''['' + brk_srb.name + '']''
WHEN ''ROUTE'' THEN ''['' + brk_sr.name + '']''
WHEN ''SERVICE'' THEN ''['' + brk_s.name + '']''
WHEN ''XML_SCHEMA_COLLECTION'' THEN ''['' + xml_sc.sch + ''].['' + xml_sc.xml_schema_collection + '']''
WHEN ''SYMMETRIC_KEYS'' THEN ''['' + k.name + '']''
WHEN ''CERTIFICATE'' THEN ''['' + c.name + '']''
WHEN ''ASYMMETRIC_KEY'' THEN ''['' + ak.name + '']''
ELSE CONVERT(NVARCHAR(MAX),perm.major_id)
END AS [ObjectName]
, CASE class_desc
WHEN ''OBJECT_OR_COLUMN'' THEN COALESCE(''['' + oc.col + '']'', ''['' + soc.col + '']'')
ELSE NULL
END AS [ColumnName]
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals sp
ON sp.sid = dp.sid
INNER JOIN sys.database_permissions AS perm
ON perm.grantee_principal_id = dp.principal_id
INNER JOIN sys.database_principals AS grantor
ON grantor.principal_id = perm.grantor_principal_id
'
SET @ReqBase = @ReqBase +
'LEFT JOIN sys.service_message_types brk_smt
ON brk_smt.message_type_id = perm.major_id
AND perm.class_desc = ''MESSAGE_TYPE''
LEFT JOIN sys.service_contracts brk_sc
ON brk_sc.service_contract_id = perm.major_id
AND perm.class_desc = ''SERVICE_CONTRACT''
LEFT JOIN sys.remote_service_bindings brk_srb
ON brk_srb.remote_service_binding_id = perm.major_id
AND perm.class_desc = ''REMOTE_SERVICE_BINDING''
LEFT JOIN sys.routes brk_sr
ON brk_sr.route_id = perm.major_id
AND perm.class_desc = ''ROUTE''
LEFT JOIN sys.services brk_s
ON brk_s.service_id = perm.major_id
AND perm.class_desc = ''SERVICE''
'
SET @ReqBase = @ReqBase +
'LEFT JOIN (
SELECT xml_sc.xml_collection_id, s.name AS sch, xml_sc.name AS xml_schema_collection
FROM sys.xml_schema_collections xml_sc
INNER JOIN sys.schemas s
ON xml_sc.schema_id = s.schema_id
) xml_sc
ON xml_sc.xml_collection_id = perm.major_id
AND perm.class_desc = ''XML_SCHEMA_COLLECTION''
LEFT JOIN sys.fulltext_catalogs ftxt
ON ftxt.fulltext_catalog_id = perm.major_id
AND perm.class_desc = ''FULLTEXT_CATALOG''
LEFT JOIN sys.assemblies ass
ON ass.assembly_id = perm.major_id
AND perm.class_desc = ''ASSEMBLY''
LEFT JOIN sys.schemas sch
ON sch.schema_id = perm.major_id
AND perm.class_desc = ''SCHEMA''
LEFT JOIN sys.database_principals u
ON u.principal_id = perm.major_id
AND perm.class_desc = ''DATABASE_PRINCIPAL''
LEFT JOIN sys.certificates c
ON c.certificate_id = perm.major_id
AND perm.class_desc = ''CERTIFICATE''
'
SET @ReqBase = @ReqBase +
'LEFT JOIN sys.asymmetric_keys ak
ON ak.asymmetric_key_id = perm.major_id
AND perm.class_desc = ''ASYMMETRIC_KEY''
LEFT JOIN sys.symmetric_keys k
ON k.symmetric_key_id = perm.major_id
AND perm.class_desc = ''SYMMETRIC_KEYS''
LEFT JOIN (
SELECT t.user_type_id, s.name AS sch, t.name AS Typ
FROM sys.types t
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
) t
ON t.user_type_id = perm.major_id
AND perm.class_desc = ''TYPE''
'
SET @ReqBase = @ReqBase + '
LEFT JOIN (
SELECT o.object_id, c.column_id, o.name AS Obj, s.name AS Sch, c.name AS Col
FROM sys.objects o
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
INNER JOIN sys.columns c
ON c.object_id = o.object_id
) oc
ON oc.object_id = perm.major_id
AND (oc.column_id = perm.minor_id)
AND perm.class_desc = ''OBJECT_OR_COLUMN''
LEFT JOIN (
SELECT o.object_id, o.name AS Obj, s.name AS Sch
FROM sys.objects o
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
) o
ON o.object_id = perm.major_id
AND perm.minor_id = 0
AND perm.class_desc = ''OBJECT_OR_COLUMN''
'
SET @ReqBase = @ReqBase + '
LEFT JOIN (
SELECT o.object_id, c.column_id, o.name AS Obj, s.name AS Sch, c.name AS Col
FROM sys.system_objects o
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
INNER JOIN sys.system_columns c
ON c.object_id = o.object_id
) soc
ON soc.object_id = perm.major_id
AND (soc.column_id = perm.minor_id)
AND perm.class_desc = ''OBJECT_OR_COLUMN''
LEFT JOIN (
SELECT o.object_id, o.name AS Obj, s.name AS Sch
FROM sys.system_objects o
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
) so
ON so.object_id = perm.major_id
AND perm.minor_id = 0
AND perm.class_desc = ''OBJECT_OR_COLUMN''
WHERE sp.Type IN (''S'',''U'',''G'')
' + CASE WHEN @LoginSource IS NULL THEN '' ELSE 'AND sp.name = ''' + @LoginSource + '''' END + '
'
EXEC(@ReqBase)
FETCH NEXT FROM ListDB
INTO @Name
END
CLOSE ListDB;
DEALLOCATE ListDB;
/* Récupération du mot de passe et du SID en fonction paramétrage */
DECLARE @BlocPWD NVARCHAR(MAX)
IF OBJECT_ID('tempdb..#LoginPWD') IS NOT NULL
DROP TABLE #LoginPWD
CREATE TABLE #LoginPWD
(
LoginName SYSNAME
, PWD VARCHAR(MAX)
, SID VARCHAR(MAX)
)
IF @ExtractScript = 1
BEGIN
IF @WithEvenPWD = 0
BEGIN
IF @DefaultPWD = 1
SET @BlocPWD = '''' + @newPWD + ''' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON'
ELSE
BEGIN
SET @BlocPWD = '''' + @newPWD + ''''
END
END
ELSE
BEGIN
DECLARE @Login SYSNAME
DECLARE @SID varbinary(256)
DECLARE @newSID VARCHAR(MAX)
DECLARE @PWD_varbinary VARBINARY(256)
DECLARE lPwD CURSOR FOR
SELECT DISTINCT loginname, SID
FROM #ServerRole_Login
WHERE LoginType = 'SQL_LOGIN'
OPEN lPwD
FETCH NEXT FROM lPwD
INTO @Login, @SID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PWD_varbinary = CAST(LOGINPROPERTY(@Login,'PasswordHash') AS VARBINARY(256))
EXEC sp_hexadecimal @PWD_varbinary, @newPWD OUT
EXEC sp_hexadecimal @SID, @newSID OUT
INSERT INTO #LoginPWD VALUES (@Login, @newPWD, @newSID)
FETCH NEXT FROM lPwD
INTO @Login, @SID
END
CLOSE lPwD;
DEALLOCATE lPwD;
END
END
IF @AuditRight = 1
BEGIN
IF EXISTS (SELECT TOP 1 1 FROM #LoginPWD)
SELECT DISTINCT 'Config login : ' AS Titre, l.LoginName, LoginType, is_disabled, default_database_name, default_language_name, policy_checked, expiration_checked
, p.sid, p.PWD
FROM #ServerRole_Login l
INNER JOIN #LoginPWD p
ON p.LoginName = l.LoginName
ELSE
SELECT DISTINCT 'Config login : ' AS Titre, l.LoginName, LoginType, is_disabled, default_database_name, default_language_name, policy_checked, expiration_checked
FROM #ServerRole_Login l
SELECT 'Role Serveur : ' AS Titre, LoginName, ServerRole FROM #ServerRole_Login
SELECT 'Permission serveur : ' AS Titre, * FROM #ServerPermission_Login
SELECT 'Mapping Login/Credential : ' AS Titre, * FROM #LoginCredential
SELECT 'Mapping Login/User : ' AS Titre,* FROM #Login_User ORDER BY DataBaseName
SELECT 'User Orphelin : ' AS Titre, * FROM #User_Orphelins ORDER BY DataBaseName
SELECT 'Role DB : ' AS Titre,* FROM #DataBaseRole_User ORDER BY DataBaseName
--permission base
SELECT 'Permission DB : ' AS Titre,* FROM #DataBasePermission_User ORDER BY DataBaseName
END
IF @ExtractScript = 1
BEGIN
IF EXISTS (SELECT TOP 1 1 FROM #User_Orphelins)
BEGIN
SELECT '/!\ Des users orphelins sont présents, ils sont indiqués pour information mais non remontés dans le résulat final /!\'
SELECT 'User orphelin : ' AS Titre, * FROM #User_Orphelins
END
IF EXISTS (SELECT TOP 1 1 FROM #LoginCredential)
BEGIN
SELECT '/!\ Des logins sont mappés sur des crédentials, ce script ne gère pas le mapping login - credential /!\'
SELECT 'Mapping Login/Credential : ' AS Titre, * FROM #User_Orphelins
END
END
IF @ExtractScript = 1
BEGIN
SELECT *
FROM (
SELECT DISTINCT 'Suppression login : ' AS Titre, 'USE [master]
GO
IF EXISTS(SELECT * FROM sys.server_principals WHERE name = ''' + ISNULL(@LoginDest, LoginName) + ''')
DROP LOGIN [' + ISNULL(@LoginDest, LoginName) + ']' AS Script
FROM #ServerRole_Login
WHERE @AddRight = 0
UNION ALL
SELECT DISTINCT 'Création Login : ' AS Titre
, 'USE [master]
GO
IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = ''' + ISNULL(@LoginDest, l.LoginName) + ''')
BEGIN
CREATE LOGIN [' + ISNULL(@LoginDest, l.LoginName) + '] ' + CASE WHEN LoginType IN ('WINDOWS_LOGIN','WINDOWS_GROUP') THEN 'FROM WINDOWS' ELSE '' END COLLATE FRENCH_CI_AS + '
WITH' + CASE WHEN LoginType = 'SQL_LOGIN' THEN ' PASSWORD=' + ISNULL(@BlocPWD,p.PWD + ' HASHED')+ ',' + CASE WHEN @LoginDest IS NULL THEN ' SID = ' + p.SID + ',' ELSE '' END ELSE '' END + '
DEFAULT_DATABASE=[' + default_database_name COLLATE FRENCH_CI_AS +'], DEFAULT_LANGUAGE=[' + default_language_name COLLATE FRENCH_CI_AS + ']
END
' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + ISNULL(@LoginDest, l.LoginName) + '] DISABLE' ELSE 'ALTER LOGIN [' + ISNULL(@LoginDest, l.LoginName) + '] ENABLE' END AS Script
FROM #ServerRole_Login l
LEFT JOIN #LoginPWD p
ON p.LoginName = l.LoginName
UNION ALL
/*on passe ar addrolemember car alter login pour assigner des droits marche sous 2012 mais pas les versions précédentes*/
SELECT 'Role Serveur : ' AS Titre
, 'EXEC master..sp_addsrvrolemember @loginame = N''' + ISNULL(@LoginDest, LoginName) + ''', @rolename = N''' + ServerRole + '''' AS Script
FROM #ServerRole_Login
WHERE ServerRole <> 'public'
UNION ALL
SELECT 'Permission Serveur : ' AS Titre
, 'USE [master]
' + state_desc + ' ' + ServerPermission + ' TO [' + ISNULL(@LoginDest, LoginName) + ']' + CASE WHEN isGrantOption = 1 THEN ' WITH GRANT OPTION' ELSE '' END AS Script
FROM #ServerPermission_Login
WHERE class_desc = 'SERVER'
UNION ALL
SELECT 'Permission Serveur : ' AS Titre
, 'USE [master]
' + state_desc + ' ' + ServerPermission + ' ON LOGIN::[' + major + '] TO [' + ISNULL(@LoginDest, LoginName) + ']' + CASE WHEN isGrantOption = 1 THEN ' WITH GRANT OPTION' ELSE '' END AS Script
FROM #ServerPermission_Login
WHERE class_desc = 'SERVER_PRINCIPAL'
UNION ALL
SELECT 'Permission Serveur : ' AS Titre
, 'USE [master]
' + state_desc + ' ' + ServerPermission + ' ON ENDPOINT::[' + major + '] TO [' + ISNULL(@LoginDest, LoginName) + ']' + CASE WHEN isGrantOption = 1 THEN ' WITH GRANT OPTION' ELSE '' END AS Script
FROM #ServerPermission_Login
WHERE class_desc = 'ENDPOINT'
UNION ALL
SELECT DISTINCT 'Suppression User : ' AS Titre, *
FROM (
SELECT 'USE [' + DataBaseName + ']
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + ISNULL(@LoginDest, UserName) + ''')
DROP USER [' + ISNULL(@LoginDest, UserName) + ']' AS Script
FROM #Login_User
UNION
SELECT 'USE [' + DataBaseName + ']
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + ISNULL(@LoginDest, UserName) + ''')
DROP USER [' + ISNULL(@LoginDest, UserName) + ']' AS Script
FROM #DataBasePermission_User
) t
WHERE @AddRight = 0
UNION ALL
SELECT DISTINCT 'Create User : ' AS Titre, Script
FROM (
SELECT 'USE [' + DataBaseName + ']
GO
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + ISNULL(@LoginDest, UserName) + ''')
CREATE USER [' + ISNULL(@LoginDest, UserName) + '] FOR LOGIN [' + ISNULL(@LoginDest, LoginName) + ']' + CASE WHEN Default_Schema_Name IS NOT NULL THEN ' WITH DEFAULT_SCHEMA=[' + Default_Schema_Name + ']' ELSE '' END AS script, DataBaseName
FROM #Login_User
) t
UNION ALL
/*on passe ar addrolemember car alter user pour assigner des droits marche sous 2012 mais pas les versions précédentes*/
SELECT 'Role DB : ' AS Titre, 'USE [' + DataBaseName + ']
GO
EXEC sp_addrolemember N''' + DataBaseRole COLLATE FRENCH_CI_AS + ''', N''' + ISNULL(@LoginDest, UserName) + '''' AS Script
FROM #DataBaseRole_User
UNION ALL
SELECT 'Permission DB : ' AS Titre, 'USE [' + DataBaseName + ']
' + State COLLATE FRENCH_CI_AS
+ ' ' + DBPermission COLLATE FRENCH_CI_AS
+ ' TO [' + ISNULL(@LoginDest, UserName) COLLATE FRENCH_CI_AS + ']'
+ CASE isGrantOption WHEN 1 THEN ' WITH GRANT OPTION' ELSE '' END AS script
FROM #DataBasePermission_User
WHERE class_desc = 'DATABASE'
UNION ALL
SELECT 'Permission DB : ' AS Titre, 'USE [' + DataBaseName + ']
' + State COLLATE FRENCH_CI_AS
+ ' ' + DBPermission COLLATE FRENCH_CI_AS
+ ' ON ' + REPLACE(CASE class_desc
WHEN 'OBJECT_OR_COLUMN' THEN 'OBJECT'
WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY'
WHEN 'DATABASE_PRINCIPAL' THEN 'USER'
WHEN 'SERVICE_CONTRACT' THEN 'CONTRACT'
ELSE class_desc END,'_',' ') COLLATE FRENCH_CI_AS
+ '::' + ObjectName COLLATE FRENCH_CI_AS +
+ ISNULL('(' + ColumnName COLLATE FRENCH_CI_AS + ')','')
+ ' TO [' + ISNULL(@LoginDest, UserName) COLLATE FRENCH_CI_AS + ']'
+ CASE isGrantOption WHEN 1 THEN ' WITH GRANT OPTION' ELSE '' END AS script
from #DataBasePermission_User
WHERE class_desc <> 'DATABASE'
) t
END
Finish:
END
GO