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

Click to rate this post! [Total: 0 Average: 0]