Comment créer un script des mappages d'utilisateurs pour les connexions Utilisateurs? – Serveur d’impression

Vous ne savez pas si c'est exactement ce dont vous avez besoin, mais jetez un coup d'œil:

/ * ============================================= =============================================== ===================== * /
/ * = Documentation d'audit d'audit de sécurité d'instance = * /
/ * ============================================= =============================================== ===================== * /
/ * Date de création: 28/12/2011
 Par: VikingDBA
 Modifications:
 08/07/2012 Modifié pour faire aussi des autorisations au niveau du serveur (comme VIEW ANY DATABASE, ou VIEW SERVER STATE)
 01/09/2013 Changement pour imprimer les scripts afin de créer les instructions CREATE ROLE et CREATE USER
 31/01/2013 Modifié pour imprimer les instructions de niveau serveur et les instructions pour définir les valeurs par défaut
bases de données pour les utilisateurs
 
Dépendances:
 Ce script dépend des éléments suivants pour exister:
 aucun

 Sommaire:
 Ce script crée la documentation de toutes les bases de données d’une instance, y compris les autorisations au niveau du serveur et le rôle de la base de données.
 autorisations et autorisations individuelles des objets de base de données.

Notez que le fait de changer les ensembles @outputtype = 1 pour columnar (style de rapport) et que la valeur 2 crée les instructions d'affectation

 REMARQUE: s'exécute pour toutes les bases de données, sauf si une base de données spécifique est utilisée dans la clause WHERE ci-dessous.
 Consultez également la section Variables à définir par l'utilisateur ci-dessous pour savoir si des variables doivent être définies.
* /

Utilisez MASTER

SET NOCOUNT ON


/ * ============================================= =============================================== ================== * /
- Audit de sécurité pour les rôles de serveur
DECLARE @sr varchar (100)
DECLARE @mn varchar (150)
DECLARE @cmd varchar (4000)
DECLARE @ col1nm varchar (200)
DECLARE @ col2nm varchar (200)
DECLARE @ col3nm varchar (200)
DECLARE @ col4nm varchar (200)
DECLARE @ col5nm varchar (200)
DECLARE @ col6nm varchar (200)
DECLARE @ col7nm varchar (200)
DECLARE @ col8nm varchar (200)
DECLARE @ col9nm varchar (200)
DECLARE @ col10nm varchar (200)
DECLARE @ col11nm varchar (200)
DECLARE @ col12nm varchar (200)
DECLARE @ col13nm varchar (200)
DECLARE @ col14nm varchar (200)
DECLARE @ col15nm varchar (200)
DECLARE @ col16nm varchar (200)
DECLARE @ col17nm varchar (200)
DECLARE @ col18nm varchar (200)
DECLARE @ col19nm varchar (200)
DECLARE @ col20nm varchar (200)
DECLARE @ col1len int
DECLARE @ col2len int
DECLARE @ col3len int
DECLARE @ col4len int
DECLARE @ col5len int
DECLARE @ col6len int
DECLARE @ col7len int
DECLARE @ col8len int
DECLARE @ col9len int
DECLARE @ col10len int
DECLARE @ col11len int
DECLARE @ col12len int
DECLARE @ col13len int
DECLARE @ col14len int
DECLARE @ col15len int
DECLARE @ col16len int
DECLARE @ col17len int
DECLARE @ col18len int
DECLARE @ col19len int
DECLARE @ col20len int
DECLARE @ col1max int
DECLARE @ col2max int
DECLARE @ col3max int
DECLARE @ col4max int
DECLARE @ col5max int
DECLARE @ col6max int
DECLARE @ col7max int
DECLARE @ col8max int
DECLARE @ col9max int
DECLARE @ col10max int
DECLARE @ col11max int
DECLARE @ col12max int
DECLARE @ col13max int
DECLARE @ col14max int
DECLARE @ col15max int
DECLARE @ col16max int
DECLARE @ col17max int
DECLARE @ col18max int
DECLARE @ col19max int
DECLARE @ col20max int
DECLARE @ col1min int
DECLARE @ col2min int
DECLARE @ col3min int
DECLARE @ col4min int
DECLARE @ col5min int
DECLARE @ col6min int
DECLARE @ col7min int
DECLARE @ col8min int
DECLARE @ col9min int
DECLARE @ col10min int
DECLARE @ col11min int
DECLARE @ col12min int
DECLARE @ col13min int
DECLARE @ col14min int
DECLARE @ col15min int
DECLARE @ col16min int
DECLARE @ col17min int
DECLARE @ col18min int
DECLARE @ col19min int
DECLARE @ col20min int
DECLARE @rn varchar (200)
DECLARE @un varchar (200)
DECLARE @ut varchar (200)
DECLARE @sd varchar (200)
DECLARE @pn varchar (200)
DECLARE @sn varchar (200)
DÉCLARE @on varchar (200)
DECLARE @pd varchar (200)
DÉCLARE @sdmax int
DECLARE @pnmax int
DÉCLARE @snmax int
DÉCLARER @onmax int
DECLARE @pdmax int
DECLARE @unmax int
DECLARE @rnmax int
DÉCLARE @utmax int
DECLARE @outputtype int
DECLARE @prodlevel varchar (25)
DECLARE @version varchar (250)
DEClARE @prodver varchar (50)
DECLARE @edition varchar (50)
DÉCLARER bit @includeobjlvlperms
DECLARE @includeroleinfo bit
DECLARE @includedefaultdb bit
DECLARE @usnm varchar (128)
DÉCLARE @ustp varchar (60)
DECLARE @stdsc varchar (60)
DECLARE @permnm varchar (128)
DECLARE @collationname varchar (200)
DECLARE @lineval varchar (2000)
DECLARE @loginname varchar (100)
DECLARE @dbnametouse sysname

/ * ============================================= ============================================== * /
- Variables paramétrables par l'utilisateur
SET @outputtype = 1 - 1 = en colonne 2 = instructions d'affectation
SET @includeobjlvlperms = 1
SET @includeroleinfo = 1
SET @includedefaultdb = 1
/ * ============================================= ============================================== * /

SELECT @ prodlevel = CONVERT (varchar (25), SERVERPROPERTY ('ProductLevel'))
SELECT @ version = CONVERT (varchar (250), @@ VERSION)
SELECT @ prodver = CONVERT (varchar (50), SERVERPROPERTY ('ProductVersion'))
SELECT @ edition = CONVERT (varchar (50), SERVERPROPERTY ('Edition'))
/ * ============================================= ============================= /
--Trouver une ligne de démarcation
DECLARE @lvaltouse varchar (2000)
DECLARE @lvallength int
DECLARE @lvalct int
DECLARE @spotcat int
DECLARE @spotcatval int
DECLARE @ lval1 varchar (2000)
DECLARE @ lval2 varchar (2000)
DECLARE @ lval3 varchar (2000)
DECLARE @ lval4 varchar (2000)
DECLARE @ lval5 varchar (2000)
DECLARE @ lval6 varchar (2000)
SET @lvaltouse = @version
SET @lvallength = LEN (@lvaltouse)
SET @lvalct = 1
SET @spotcat = 1
SET @ lval1 = ''
SET @ lval2 = ''
SET @ lval3 = ''
SET @ lval4 = ''
SET @ lval5 = ''
SET @ lval6 = ''
PENDANT @spotcat <= @lvallength
 COMMENCER
 SET @spotcatval = ASCII (SUBSTRING (@ lvaltouse, @ spotcat, 1))
 if @spotcatval = 10 - valeur recherchée
 SET @lvalct = @lvalct + 1 - défini pour passer à la ligne suivante et commencer à la construire
 else - ajoute à la ligne de valeur actuelle
 COMMENCER
 if @spotcatval <> 9 - valeurs que nous voulons exclure
 COMMENCER
 si @lvalct = 1
 SET @ lval1 = @ lval1 + CHAR (@spotcatval)
 si @lvalct = 2
 SET @ lval2 = @ lval2 + CHAR (@spotcatval)
 si @lvalct = 3
 SET @ lval3 = @ lval3 + CHAR (@spotcatval)
 si @lvalct = 4
 SET @ lval4 = @ lval4 + CHAR (@spotcatval)
 si @lvalct = 5
 SET @ lval5 = @ lval5 + CHAR (@spotcatval)
 si @lvalct = 6
 SET @ lval6 = @ lval6 + CHAR (@spotcatval)
 FIN
 FIN
 SET @spotcat = @spotcat + 1
 FIN
 --PRINT 'Ligne à scinder =' + @lvaltouse
 --PRINT 'line1 =' + @ lval1
 --PRINT 'line2 =' + @ lval2
 --PRINT 'line3 =' + @ lval3
 --PRINT 'line4 =' + @ lval4
 --PRINT 'line5 =' + @ lval5
 --PRINT 'line6 =' + @ lval6
/ * ============================================= ============================== /
CREATE TABLE #dummyuserassign
(RecID int IDENTITY,
LineVal varchar (2000)
)

IMPRIMER '============================================= =============================================== =========== '
IMPRIMER 'Audit de sécurité pour instance de serveur' + CONVERT (varchar (128), @@ nomserveur)
si @outputtype = 2
 IMPRIMER 'Déclarations d'attribution'
PRINT 'For' + CONVERT (varchar (128), getdate (), 101) + '' + CONVERT (varchar (128), getdate (), 108)
IMPRIMER '============================================= =============================================== =========== '
PRINT 'Version du serveur SQL:' + @ lval1
IMPRIMER '' + @ lval4
IMPRIMER '============================================= =============================================== =========== '
PRINT 'REMARQUE: veillez à obtenir la liste des connexions à l'aide de la procédure stockée sp_help_revlogin de la base de données master.'
IMPRIMER '============================================= =============================================== =========== '
IMPRIMER 'Paramètres de sécurité du rôle serveur'
IMPRESSION ' '
IMPRESSION ' '




CREATE TABLE #rolememberdummy
(ServerRole varchar (100),
Nom du membre varchar (150),
MemberSID varchar (2000)
)
CREATE TABLE #dummyDBPerms
(StateDesc varchar (200),
 PermName varchar (200),
 SchemaName varchar (200),
 ObjectName varchar (200),
 Nom d'utilisateur varchar (200),
 ObjectType varchar (200),
 Type d'utilisateur varchar (200)
)


- Audit de sécurité
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'sysadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'securityadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'serveradmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'dbcreator'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'diskadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'processadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'setupadmin'
INSERT INTO #rolememberdummy
EXEC sp_helpsrvrolemember 'bulkadmin'



SET @ col1nm = 'Role'
SET @ col1len = 20
SET @ col2nm = ''
SET @ col2len = 8
SET @ col3nm = 'Nom du membre'
SET @ col3len = 30
IMPRIMER @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm
PRINT REPLICATE ('=', @ col1len) + ESPACE (@ col2len) + REPLICATE ('=', @ col3len)






--SELECT CONVERT (varchar (30), ServerRole) en tant que ServerRole, CONVERT (varchar (30), MemberName) AS MemberName FROM #rolememberdummy
DÉCLARER backupfiles CURSEUR POUR
SELECT ServerRole, MemberName FROM #rolememberdummy

OPEN backupFiles

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM backupFiles INTO @sr, @mn

WHILE @@ FETCH_STATUS = 0
COMMENCER
SET @ col1nm = @sr
 SET @ col1len = 20
 SET @ col2nm = ''
 SET @ col2len = 8
 SET @ col3nm = @mn
 SET @ col3len = 30
 IMPRIMER @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm


 FETCH NEXT FROM backupFiles INTO @sr, @mn
FIN

FERMER backupFiles
DEALLOCATE backupfiles

DROP TABLE #rolememberdummy


IMPRESSION ' '
IMPRESSION ' '
IMPRIMER '============================================= =============================================== ========= '
IMPRIMER 'Autorisations au niveau du serveur'
IMPRESSION ' '
IMPRESSION ' '

CREATE TABLE #serverpermdummy
(Nom d'utilisateur varchar (128),
Type d'utilisateur varchar (60),
StateDesc varchar (60),
PermName varchar (128)
)

INSERT INTO #serverpermdummy
SELECT l.name comme nom d'utilisateur, l.type_desc AS UserType, p.state_desc AS StateDesc, p.permission_name AS PermName
FROM sys.server_permissions AS p
JOIN sys.server_principals AS l ON p.grantee_principal_id = l.principal_id
WHERE ((nom_autorisation <> 'CONNECT SQL' ET nom_autorisation <> 'CONNECT') OU p.state_desc = 'DENY') ET l.type_desc <> 'CERTIFICATE_MAPPED_LOGIN' ET l.nom NE PAS LIKE '% # MS_%'
ORDER BY l.principal_id

--SELECT * FROM sys.server_principals

SET @ col1nm = 'Nom d'utilisateur'
SET @ col1len = 20
SET @ col2nm = ''
SET @ col2len = 8
SET @ col3nm = 'Type d'utilisateur'
SET @ col3len = 20
SET @ col4nm = ''
SET @ col4len = 8
SET @ col5nm = 'State Desc'
SET @ col5len = 20
SET @ col6nm = ''
SET @ col6len = 8
SET @ col7nm = 'Permission'
SET @ col7len = 30

SET @ col1min = LEN (@ col1nm)
SET @ col3min = LEN (@ col3nm)
SET @ col5min = LEN (@ col5nm)
SET @ col7min = LEN (@ col7nm)

--Obtenir la longueur de la plus longue occurrence des colonnes
SELECT @ col1max = ISNULL (MAX (len (LTRIM (RTRIM (UserName)))), 0) FROM #serverpermdummy
SELECT @ col3max = ISNULL (MAX (len (LTRIM (RTRIM (UserType)))), 0) FROM #serverpermdummy
SELECT @ col5max = ISNULL (MAX (len (LTRIM (RTRIM (StateDesc)))), 0) FROM #serverpermdummy
SELECT @ col7max = ISNULL (MAX (len (LTRIM (RTRIM (PermName)))), 0) FROM #serverpermdummy


- Définissez des valeurs minimales pour que la colonne ne s'imprime pas rapidement
if @ col1max <@ col1min SET @ col1len = @ col1min sinon SET @ col1len = @ col1max
si @ col3max <@ col3min SET @ col3len = @ col3min sinon SET @ col3len = @ col3max
si @ col5max <@ col5min SET @ col5len = @ col5min sinon SET @ col5len = @ col5max
si @ col7max <@ col7min SET @ col7len = @ col7min sinon SET @ col7len = @ col7max

si @outputtype = 1
 COMMENCER
 IMPRIMER @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len -len (@ col5nm)) + ESPACE (@ col6len) + @ col7nm + ESPACE (@ col7len-len (@ col7nm))
 PRINT REPLICATE ('=', @ col1len) + ESPACE (@ col2len) + REPLICATE ('=', @ col3len) + SPACE (@ col4len) + REPLICAT ('=', @ col5len) + SPACE (@ col6len) + REPLICATE ('=', @ col7len)
 FIN
autre
 si EXISTS (SELECT ISNULL (UserName, '') AS UserName, ISNULL (UserType, '') AS UserType, ISNULL (StateDesc, '') AS StateDesc, ISNULL (PermName, '') AS PermName FROM nom_serveurpermdummy)
 IMPRIMER 'UTILISER le maître;'


DÉCLARER backupfiles CURSEUR POUR
SELECT ISNULL (UserName, '') AS UserName, ISNULL (UserType, '') AS UserType, ISNULL (StateDesc, '') AS StateDesc, ISNULL (PermName, '') AS PermName FROM #serverpermdummy

OPEN backupFiles

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm


WHILE @@ FETCH_STATUS = 0
COMMENCER
SET @ col1nm = @usnm
 SET @ col2nm = ''
 SET @ col3nm = @ustp
 SET @ col4nm = ''
 SET @ col5nm = @stdsc
 SET @ col6nm = ''
 SET @ col7nm = @permnm
 si @outputtype = 1
 IMPRIMER @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len -len (@ col5nm)) + ESPACE (@ col6len) + @ col7nm + ESPACE (@ col7len-len (@ col7nm))
 autre
 PRINT @stdsc + '' + @permnm + 'TO' + @usnm + ';'


 FETCH NEXT FROM backupFiles INTO @usnm, @ustp, @stdsc, @permnm
FIN

FERMER backupFiles
DEALLOCATE backupfiles


DROP TABLE #serverpermdummy

IMPRESSION ' '
IMPRESSION ' '
IMPRIMER '============================================= =============================================== ========= '
IMPRIMER 'Information par base de données'
IMPRESSION ' '
IMPRESSION ' '

CREATE TABLE #DummyDBDesc
(RecID int IDENTITY NOT NULL,
 NomServeur varchar (128) NULL,
 DBName varchar (100) NULL,
 Modèle de restauration varchar (10) NULL,
 CompatibilityLevel varchar (30) NULL,
 ReadWriteDesc varchar (10) NULL
)
CREATE TABLE #dummyDBRoles
(RoleName varchar (200),
 Nom d'utilisateur varchar (200),
 Type d'utilisateur varchar (200)
)

CREATE TABLE #dummyrolelist
(Nom de rôle varchar (200)
)

CREATE TABLE #dummyDBUsers
(Nom d'utilisateur varchar (200),
 Type d'utilisateur varchar (200)
)
INSERT INTO #DummyDBDesc
sélectionnez CONVERT (varchar (128), @@ nom_serveur) AS ServerName, CONVERT (varchar (100), nom) en tant que DBName, CONVERT (varchar (10), recovery_model_desc) en tant que RecoveryModel, --database Base,
CASE niveau_compatibilité
WHEN 80 THEN CONVERT (varchar (4), niveau_compatibilité) + '- SQL 2000 *'
WHEN 90 THEN CONVERT (varchar (4), niveau_compatibilité) + '- SQL 2005'
WHEN 100 THEN CONVERT (varchar (4), niveau_compatibilité) + '- SQL 2008'
WHEN 105 THEN CONVERT (varchar (4), niveau_compatibilité) + '- SQL 2008 R2'
WHEN 110 THEN CONVERT (varchar (4), niveau_compatibilité) + '- SQL 2012'
ELSE CONVERT (varchar (4), niveau_compatibilité)
END AS CompatibilityLevel,
CASE is_read_only
QUAND 0 ALORS CONVERTIR (varchar (10), 'RW')
ELSE CONVERT (varchar (10), 'R')
FIN comme ReadWriteDesc
FROM sys.databases
WHERE nom NOT IN ('tempdb', 'master', 'msdb', 'model') et nom NOT LIKE '% ReportServer%' AND state = 0
--AND name = 'MyDatabase'
ORDRE PAR NOM


DÉCLARER backupfiles CURSEUR POUR
 SELECT DBName, RecoveryModel, CompatibilityLevel, ReadWriteDesc FROM #DummyDBDesc ORDER BY DBName
OPEN backupFiles

DECLARE @dbn varchar (100)
DECLARE @rm varchar (10)
DECLARE @cl varchar (30)
DECLARE @rwd varchar (10)


- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM backupFiles INTO @dbn, @rm, @cl, @rwd

WHILE @@ FETCH_STATUS = 0
COMMENCER

IMPRIMER 'Nom de la base de données:' + @dbn
 PRINT 'Modèle de récupération:' + @rm
 PRINT 'Niveau de compatibilité:' + @cl
 IMPRIMER 'Lecture / Ecriture:' + @rwd
 IMPRESSION ' '
 IMPRESSION ' '
 

/ * ============================================= =============================================== =============================================== ============= * /
 / * Informations sur l'utilisateur de la base de données * /
 
--Démarrez avec une table vierge pour charger les valeurs
 TRUNCATE TABLE #dummyDBUsers

 - Obtenez les rôles pour cette base de données et chargez-les dans la table temporaire
 SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBUsers SELECT CONVERT (varchar (100), nom) AS UserName, CONVERT (varchar (100), type_desc) en tant que UserType FROM sys.database_principals WHERE (type = '' '' 'OU type =' '' '' OU type = '' G '') AND is_fixed_role = 0 AND (nom NOT IN ('' guest '', '' dbo '', '' INFORMATION_SCHEMA '', '' sys ''))
 --PRINT @cmd
 EXEC (@cmd)

--Obtenir la longueur de la plus longue occurrence des colonnes
 SELECT @unmax = ISNULL (MAX (len (UserName)), 0) FROM #dummyDBUsers
 SELECT @utmax = ISNULL (MAX (len (UserType)), 0) FROM #dummyDBUsers

 - Définissez des valeurs minimales pour que la colonne ne s'imprime pas rapidement
 si @unmax <25 SET @unmax = 25
 si @utmax <25 SET @utmax = 25
 
--Définissez et imprimez les en-têtes de colonne pour les informations de rôle.
 SET @ col1nm = 'Nom d'utilisateur'
 SET @ col1len = @unmax
 SET @ col2nm = ''
 SET @ col2len = 5
 SET @ col3nm = 'UserType'
 SET @ col3len = @utmax
 IMPRESSION ' '
 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm))
ESPACE D'IMPRESSION (10) + REPLIQUE ('=', @ col1len) + ESPACE (@ col2len) + REPLIQUE ('=', @ col3len)

 DÉCLARER backupFiles2 CURSOR FOR
SELECT UserName, UserType FROM #dummyDBUsers ORDER BY UserName

 OPEN backupFiles2

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM backupFiles2 INTO @un, @ut

 WHILE @@ FETCH_STATUS = 0
COMMENCER
--Définissez et imprimez les détails de la ligne pour les informations de rôle.
 SET @ col1nm = SUBSTRING (@ un, 1, @ unmax)
 SET @ col3nm = SUBSTRING (@ ut, 1, @ utmax)

 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm))

FETCH NEXT FROM backupFiles2 INTO @un, @ut
 FIN

FERMER backupFiles2
DEALLOCATE backupFiles2
 
IMPRESSION ' '
 IMPRESSION ' '
 
if @outputtype = 2 - crée les instructions pour affecter un utilisateur à cette base de données
 COMMENCER
 TRUNCATE TABLE #dummyuserassign

 SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyuserassign select DISTINCT
 CASE members.type_desc
QUAND '' WINDOWS_USER ''
ALORS '' CREATE USER [''+ members.name + ''] POUR CONNEXION [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA =['' + members.default_schema_name + '']''
QUAND '' SQL_USER ''
 ALORS '' CREATE USER [''+ members.name + ''] POUR CONNEXION [''+ members.name+ '']'' + '' WITH DEFAULT_SCHEMA =['' + members.default_schema_name + '']''
 END AS CreateUser
 à partir de membres sys.database_principals
 jointure interne sys.database_role_members drm
 sur members.principal_id = drm.member_principal_id
 rejoindre des rôles sys.database_principals
 sur drm.role_principal_id = roles.principal_id
 où membres.nom <> '' dbo ''
 ORDER BY CreateUser '
 --PRINT @cmd
 EXEC (@cmd)


si existe (SELECT * FROM #dummyuserassign)
 COMMENCER
 IMPRIMER 'UTILISATION' + @dbn
 IMPRIMEZ 'GO'
 IMPRESSION ' '
 FIN
 DÉCLARER myCursorVariable3 CURSOR FOR
SELECT LineVal FROM #dummyuserassign ORDER BY RecID

 OUVRIR myCursorVariable3

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM myCursorVariable3 INTO @lineval

 WHILE @@ FETCH_STATUS = 0
COMMENCER
IMPRIMER @lineval

 FETCH NEXT FROM myCursorVariable3 INTO @lineval
 FIN

FERMER myCursorVariable3
DEALLOCATE myCursorVariable3
FIN
 


si @includeroleinfo = 1
 COMMENCER
 / * ============================================= =============================================== =============================================== ============= * /
 / * Informations sur le rôle * /
 SELECT @collationname = nom_collection FROM master.sys.databases WHERE nom = @dbn
 si @collationname EST NULL
 print 'null for' + @dbn

 si @collationname EST NULL
 SET @collationname = (SELECT nom_collection FROM maître.sys.databases WHERE nom = 'maître')

 SET @cmd = 'ALTER TABLE #dummyrolelist ALTER COLUMN Nom du rôle varchar (200) COLLATE' + @collationname + 'NULL'
 EXEC (@cmd)
SET @cmd = 'ALTER TABLE #dummyDBRoles ALTER COLUMN RoleName varchar (200) COLLATE' + @collationname + 'NULL'
 EXEC (@cmd)
SET @cmd = 'ALTER TABLE #dummyDBRoles ALTER COLUMN Nom d'utilisateur varchar (200) COLLATE' + @collationname + 'NULL'
 EXEC (@cmd)
SET @cmd = 'ALTER TABLE #dummyDBRoles ALTER COLUMN Type d'utilisateur varchar (200) COLLATE' + @collationname + 'NULL'
 EXEC (@cmd)

--Démarrez avec une table vierge pour charger les valeurs
 TRUNCATE TABLE #dummyDBRoles

 - Obtenez les rôles pour cette base de données et chargez-les dans la table temporaire
 SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBRoles sélectionnez CONVERT (varchar (200), roles.name) en tant que NomRôle, CONVERT (varchar (200), members.name) AS NomUtilisateur, CONVERT (varchar (200), members.type_desc) AS UserType de sys.database_principals membres internes rejoindre sys.database_role_members drm sur members.principal_id = drm.member_principal_id rejoindre interne sys.database_principals rôles sur drm.role_principal_id = roles.principal_id où members.name <> '' '' dbo '', ORDER BY membres
 --PRINT @cmd
 EXEC (@cmd)

- Ajoutez maintenant les rôles présents dans la base de données auxquels aucune personne ne leur a été affectée (ceux déjà présents dans la table temporaire)
 SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBRoles SELECT CONVERT (varchar (200), nom) AS NameName, '' --none-- '' As UserName, '' '' AS UserType FROM sys.database_principals WHERE type = '' R '' et is_fixed_role = 0 et nom <> '' public '' AND (nom NOT IN (SELECT NomRôle FROM #dummyDBRoles)) '
 --PRINT @cmd
 EXEC (@cmd)

- obtenez maintenant une liste des rôles de base de données créés et imprimez-les sous forme d'instructions CREATE ROLE
 si @outputtype = 2
 COMMENCER
 TRUNCATE TABLE #dummyrolelist
 SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyrolelist SELECT nom FROM sys.database_principals WHERE type = '' R '' AND nom <> '' public '' AND is_fixed_role = 0 '
 --PRINT @cmd
 EXEC (@cmd)
IMPRIMER 'UTILISATION' + @dbn
 IMPRIMEZ 'GO'
 IMPRESSION ' '
 DÉCLARER myCursorVariable4 CURSOR FOR
SELECT NomRôle FROM #dummyrolelist

OUVRIR myCursorVariable4

 - Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM myCursorVariable4 INTO @rn

 WHILE @@ FETCH_STATUS = 0
COMMENCER
SET @cmd = 'CREATE ROLE' + @rn
 IMPRIMER @cmd


FETCH NEXT FROM myCursorVariable4 INTO @rn
 FIN

FERMER myCursorVariable4
 DEALLOCATE myCursorVariable4
 FIN

 --Obtenir la longueur de la plus longue occurrence des colonnes
 SELECT @rnmax = ISNULL (MAX (len (Nom du rôle)), 0) FROM #dummyDBRoles
 SELECT @unmax = ISNULL (MAX (len (UserName)), 0) FROM #dummyDBRoles
 SELECT @utmax = ISNULL (MAX (len (UserType)), 0) FROM #dummyDBRoles

 - Définissez des valeurs minimales pour que la colonne ne s'imprime pas rapidement
 si @rnmax <25 SET @rnmax = 25
 si @unmax <25 SET @unmax = 25
 si @utmax <25 SET @utmax = 25

 --Définissez et imprimez les en-têtes de colonne pour les informations de rôle
 SET @ col1nm = 'NomRôle'
 SET @ col1len = @rnmax
 SET @ col2nm = ''
 SET @ col2len = 5
 SET @ col3nm = 'Nom d'utilisateur'
 SET @ col3len = @unmax
 SET @ col4nm = ''
 SET @ col4len = 5
 SET @ col5nm = 'UserType'
 SET @ col5len = @utmax
 IMPRESSION ' '
 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len-len (@ col5nm))
ESPACE D'IMPRESSION (10) + REPLIQUÉ ('=', @ col1len) + ESPACE (@ col2len) + REPLICAT ('=', @ col3len) + ESPACE (@ col4len) + REPLICAT ('=', @ col5len)

 - Imprimez le script pour définir le contexte de la base de données
 si @outputtype = 2
 COMMENCER
 IMPRIMER 'UTILISATION' + @dbn
 IMPRIMEZ 'GO'
 IMPRESSION ' '
 FIN

 --statement pour obtenir tous les rôles pour cette base de données
 --SELECT nom FROM sys.database_principals WHERE type = 'R' et is_fixed_role = 0 et nom <> 'public'
 --peut utiliser le script des instructions CREATE ROLE

 - Maintenant, parcourez les rôles
 DÉCLARER backupFiles2 CURSOR FOR
SELECT Nom de rôle, Nom d'utilisateur, Type d'utilisateur FROM #dummyDBRoles ORDER BY Nom de rôle

 OPEN backupFiles2

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM backupFiles2 INTO @rn, @un, @ut

WHILE @@ FETCH_STATUS = 0
COMMENCER
--Définissez et imprimez les détails de la ligne pour les informations de rôle.
 SET @ col1nm = SUBSTRING (@ rn, 1, @ rnmax)
 SET @ col3nm = SUBSTRING (@ un, 1, @ unmax)
 SET @ col5nm = SUBSTRING (@ ut, 1, @ utmax)
 si @outputtype = 1
 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len-len (@ col5nm))
si @outputtype = 2
 COMMENCER
 if @ col3nm <> '--none--'
 PRINT 'exec sp_addrolemember [' + @col1nm + '], [' + @col3nm + '] --Usertype = '+ @ col5nm
 autre
 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len-len (@ col5nm))
 FIN

 FETCH NEXT FROM backupFiles2 INTO @rn, @un, @ut
FIN

FERMER backupFiles2
DEALLOCATE backupFiles2

 IMPRESSION ' '
 IMPRESSION ' '
 FIN

 
si @includeobjlvlperms = 1
 COMMENCER
 / * ============================================= =============================================== =============================================== ============= * /
 / * Informations sur les autorisations de niveau objet * /
 --Démarrez avec une table vierge pour charger les valeurs
 TRUNCATE TABLE #dummyDBPerms

 - Obtenir les autorisations pour cette base de données et charger dans la table temporaire
 - Je suis sûr que certaines de ces pièces sont venues d'ailleurs. Mes appologies à l'initiateur.
 SET @cmd = 'USE [' + @dbn + ']; INSERT INTO #dummyDBPerms '
 SET @cmd = @cmd + 'select p.state_desc, p.permission_name, s.name, o.name, u.name, CASE o.type WHEN' 'P' 'THEN' 'SPROC' '
 QUAND '' V '' ALORS '' View ''
 QUAND '' U '' ALORS '' Table ''
 QUAND '' FN '' ALORS '' Fonction (détartreur) ''
 QUAND '' TF '' THEN '' Fonction (valeur de table) ''
 ELSE o.type_desc END AS ObjectType,
 CONVERT (varchar (200), u.type_desc) AS UserType
 de sys.database_permissions p
jointure interne sys.objects o sur p.major_id = o.object_id
jointure interne sys.schemas s sur s.schema_id = o.schema_id
jointure interne sys.database_principals u sur p.grantee_principal_id = u.principal_id
 ORDER BY o.type, o.name collate Latin1_general_CI_AS, u.name collate Latin1_general_CI_AS '
 --PRINT @cmd
 EXEC (@cmd)

--Obtenir la longueur de la plus longue occurrence de chacune des colonnes
 SELECT @sdmax = ISNULL (MAX (len (StateDesc)), 0) FROM #dummyDBPerms
 SELECT @pnmax = ISNULL (MAX (len (PermName)), 0) FROM #dummyDBPerms
 SELECT @snmax = ISNULL (MAX (len (SchemaName)), 0) FROM #dummyDBPerms
 SELECT @onmax = ISNULL (MAX (len (ObjectName)), 0) FROM #dummyDBPerms
 SELECT @unmax = ISNULL (MAX (len (UserName)), 0) FROM #dummyDBPerms
 SELECT @pdmax = ISNULL (MAX (len (ObjectType)), 0) FROM #dummyDBPerms
 SELECT @utmax = ISNULL (MAX (len (UserType)), 0) FROM #dummyDBPerms

 - Définissez des valeurs minimales pour que la colonne ne s'imprime pas rapidement
 si @sdmax <15 SET @sdmax = 15
 si @pnmax <15 SET @pnmax = 15
 si @snmax <10 SET @snmax = 10
 si @onmax <15 SET @onmax = 15
 si @unmax <15 SET @unmax = 15
 si @pdmax <15 SET @pdmax = 15 --ObjectType
 si @utmax <15 SET @utmax = 15 --UserType

 --Placez et imprimez les en-têtes de colonne pour les informations de permissions
 SET @ col1nm = 'StateDesc'
 SET @ col1len = @sdmax
 SET @ col2nm = ''
 SET @ col2len = 5
 SET @ col3nm = 'PermName'
 SET @ col3len = @pnmax
 SET @ col4nm = ''
 SET @ col4len = 5
 SET @ col5nm = 'Schema'
 SET @ col5len = @snmax
 SET @ col6nm = ''
 SET @ col6len = 5
 SET @ col7nm = 'Object'
 SET @ col7len = @onmax
 SET @ col8nm = ''
 SET @ col8len = 5
 SET @ col9nm = 'Utilisateur'
 SET @ col9len = @unmax
 SET @ col10nm = ''
 SET @ col10len = 5
 SET @ col11nm = 'ObjectType'
 SET @ col11len = @pdmax
 SET @ col12nm = ''
 SET @ col12len = 5
 SET @ col13nm = 'UserType'
 SET @ col13len = @utmax

 IMPRESSION ' '
 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len-len (@ col5nm)) + ESPACE (@ col6len) + @ col7nm + ESPACE (@ col7len-len (@ col7nm)) + ESPACE (@ col8len) + @ col9nm + ESPACE (@ col9len-len ( @ col9nm)) + ESPACE (@ col10len) + @ col11nm + ESPACE (@ col11len-len (@ col11nm)) + ESPACE (@ col12len) + @ col13nm + ESPACE (@ col13len-len (@ col13nm))
 ESPACE D'IMPRESSION (10) + REPLIQUE ('=', @ col1len) + ESPACE (@ col2len) + REPLIQUE ('=', @ col3len) + ESPACE (@ col4len) + REPLIQUE ('=', @ col5len) + ESPACE ( @ col6len) + REPLICATE ('=', @ col7len) + SPACE (@ col8len) + REPLICATE ('=', @ col9len) + SPACE (@ col10len) + REPLICATE ('=', @ col11len) + SPACE (@ col12len ) + REPLICATE ('=', @ col13len)

 --Consultez les autorisations pour cette base de données, formatez-les et imprimez-les
 DÉCLARER backupFiles2 CURSOR FOR
SELECT StateDesc, PermName, SchemaName, ObjectName, UserName, ObjectType, UserType FROM #dummyDBPerms ORDER BY Schemaname, ObjectName, UserName

 OPEN backupFiles2

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM backupFiles2 INTO @sd, @pn, @sn, @on, @un, @pd, @ut

 WHILE @@ FETCH_STATUS = 0
COMMENCER
--Placez et imprimez les détails de la ligne pour les informations sur les autorisations
 SET @ col1nm = SUBSTRING (@ sd, 1, @ sdmax)
 SET @ col3nm = SUBSTRING (@ pn, 1, @ pnmax)
 SET @ col5nm = SUBSTRING (@ sn, 1, @ snmax)
 SET @ col7nm = SUBSTRING (@ on, 1, @ onmax)
 SET @ col9nm = SUBSTRING (@ un, 1, @ unmax)
 SET @ col11nm = SUBSTRING (@ pd, 1, @ pdmax)
 SET @ col13nm = SUBSTRING (@ ut, 1, @ utmax)

 --imprime l'enregistrement de détail pour les permissions
 si @outputtype = 1
 ESPACE D'IMPRESSION (10) + @ col1nm + ESPACE (@ col1len-len (@ col1nm)) + ESPACE (@ col2len) + @ col3nm + ESPACE (@ col3len-len (@ col3nm)) + ESPACE (@ col4len) + @ col5nm + ESPACE (@ col5len-len (@ col5nm)) + ESPACE (@ col6len) + @ col7nm + ESPACE (@ col7len-len (@ col7nm)) + ESPACE (@ col8len) + @ col9nm + ESPACE (@ col9len-len ( @ col9nm)) + ESPACE (@ col10len) + @ col11nm + ESPACE (@ col11len-len (@ col11nm)) + ESPACE (@ col12len) + @ col13nm + ESPACE (@ col13len-len (@ col13nm))
 si @outputtype = 2
 PRINT @ col1nm + '' + @ col3nm + 'ON [' + @col5nm + '].[' + @col7nm + '] À [' + @col9nm + '] --ObjectType = '+ @ col11nm +' UserType = '+ @ col13nm


 FETCH NEXT FROM backupFiles2 INTO @sd, @pn, @sn, @on, @un, @ pd, @ ut
 FIN

FERMER backupFiles2
DEALLOCATE backupFiles2

 IMPRESSION ' '
 IMPRESSION ' '

 FIN


 si @outputtype = 2 ET @includedefaultdb = 1
 COMMENCER
 if EXISTS (nom SELECT de master.sys.server_Principals WHERE, saisissez ('G', 'S', 'U') ET default_database_name = @dbn)
 COMMENCER
 IMPRESSION ' '
 IMPRESSION ' '
 PRINT '- Voici les noms d'utilisateur et leurs paramètres de base de données par défaut'
 IMPRESSION ' '

 DÉCLARER CURSEUR myCursorVariable pour
SELECT nom, default_database_name comme DefaultDB
FROM master.sys.server_Principals
WHERE saisissez ('G', 'S', 'U') ET default_database_name = @dbn
 ORDRE PAR NOM

 OPEN myCursorVariable

- Boucle dans tous les fichiers de la base de données
FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse

 WHILE @@ FETCH_STATUS = 0
COMMENCER
IMPRIMER 'ALTER LOGIN [' + @loginname + '] WITH DEFAULT_DATABASE = '+ @dbnametouse

 FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse
 FIN

FERMER myCursorVariable
DEALLOCATE myCursorVariable

IMPRESSION ' '
 IMPRESSION ' '
 FIN
 FIN




IMPRIMER '============================================= =============================================== ========= '
 
--Obtenir le prochain nom de base de données et informations à utiliser dans la boucle de base de données
 FETCH NEXT FROM backupFiles INTO @dbn, @rm, @cl, @rwd
FIN

FERMER backupFiles
DEALLOCATE backupfiles

/ * ============================================= ============================================== /
--Dispose des tables temporaires
DROP TABLE #DummyDBDesc
DROP TABLE #dummyDBRoles
DROP TABLE #dummyDBUsers
DROP TABLE #dummyDBPerms
DROP TABLE #dummyuserassign
DROP TABLE #dummyrolelist

RÉGLER NOCOUNT