Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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