Le script de base de données SQL server rôle?

J'ai besoin de faire un script pour copier un particulier le rôle de base de données à partir de SQL server à l'autre.

Est-il un moyen facile de générer un script qui crée le rôle et toutes les autorisations de rôle?

InformationsquelleAutor Foster Geng | 2011-06-10

26

Vous pouvez obtenir ce dont vous avez besoin avec un script comme ceci:
```
declare @RoleName varchar(50) = 'RoleName'

declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13)
select @script = @script + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS 
from sys.database_permissions prm
    join sys.database_principals rol on
        prm.grantee_principal_id = rol.principal_id
where rol.name = @RoleName

print @script
```
- attention, si votre rôle a un grand nombre d'autorisations. J'ai trouvé que sql management studio permettra de limiter la quantité de texte que vous pouvez imprimer à l'écran. Quand j'ai couru cette requête sur ma db, il ne ramasser 151 instruction grant où j'ai effectivement eu 349. J'ai juste eu à enlever le '@script = @script' + et laissez-les résultats se retourne comme une grille.
- vous pouvez aller dans les options de gestion de studio et de modifier le texte par défaut de sortie (résultats) à 8000 de 256
InformationsquelleAutor Alex Aza

J'ai développé sur Mario Eie réponse:

SELECT 'GRANT ' + database_permissions.permission_name + ' ON ' + CASE database_permissions.class_desc
        WHEN 'SCHEMA'
            THEN '[' + schema_name(major_id) + ']'
        WHEN 'OBJECT_OR_COLUMN'
            THEN CASE 
                    WHEN minor_id = 0
                        THEN'['+OBJECT_SCHEMA_NAME(major_id) + '].' + '[' + object_name(major_id) + ']' COLLATE Latin1_General_CI_AS_KS_WS
                    ELSE (
                            SELECT object_name(object_id) + ' (' + NAME + ')'
                            FROM sys.columns
                            WHERE object_id = database_permissions.major_id
                                AND column_id = database_permissions.minor_id
                            )
                    END
        ELSE 'other'
        END + ' TO [' + database_principals.NAME + ']' COLLATE Latin1_General_CI_AS_KS_WS
FROM sys.database_permissions
JOIN sys.database_principals ON database_permissions.grantee_principal_id = database_principals.principal_id
LEFT JOIN sys.objects --left because it is possible that it is a schema
    ON objects.object_id = database_permissions.major_id
WHERE database_permissions.major_id > 0
    AND permission_name IN (
        'SELECT'
        ,'INSERT'
        ,'UPDATE'
        ,'DELETE'
        ,'EXECUTE'
        )

Merci, j'ai utilisé cette option, et depuis je n'ai besoin de ceci pour un rôle donné, j'ai viré ce sur la fin de votre script clause where: ET database_principals.NOM = 'RoleNameHere'
Si vous ajoutez "COMME PermStatement" à la fin de la clause SELECT et l ' "ORDRE PAR PermStatement" après la clause where les résultats seront triés par ordre alphabétique. Cela n'affecte pas la fonctionnalité à tous mais l'aide si vous êtes en train de comparer des autorisations pour un rôle sur deux versions différentes de la même base de données.
N'oubliez pas de créer le rôle de la première, si vous souhaitez modifier l'un spécifique qui n'existe pas encore: CRÉER un RÔLE [rolename]

InformationsquelleAutor mcfea

Ce script génère GRANT pour vos rôles. Je l'aime, qu'il prend en charge colonne leven autorisations. Il doit être adapté à votre besoin (c'est à dire. améliorer pour plus de bases de données complexes, concaténer des états et de l'exécuter, inclure l'instruction de création pour votre rôle). Mais juste pour vous donner une idée:

SELECT 'GRANT ' + database_permissions.permission_name + ' ON ' +
    CASE database_permissions.class_desc
        WHEN 'SCHEMA' THEN schema_name(major_id)
        WHEN 'OBJECT_OR_COLUMN' THEN
            CASE WHEN minor_id = 0 THEN object_name(major_id) COLLATE Latin1_General_CI_AS_KS_WS
            ELSE (SELECT object_name(object_id) + ' ('+ name + ')'
                  FROM sys.columns 
                  WHERE object_id = database_permissions.major_id 
                  AND column_id = database_permissions.minor_id) end
        ELSE 'other' 
    END + 
    ' TO ' + database_principals.name COLLATE Latin1_General_CI_AS_KS_WS
FROM sys.database_permissions
JOIN sys.database_principals
ON database_permissions.grantee_principal_id = database_principals.principal_id
LEFT JOIN sys.objects --left because it is possible that it is a schema
ON objects.object_id = database_permissions.major_id
WHERE database_permissions.major_id > 0
AND permission_name in ('SELECT','INSERT','UPDATE','DELETE')

joli petit script!
de toute façon, pour taquiner le schéma, de sorte que vous pourriez obtenir "GRANT SELECT on test.TABLE À fooUser"?
J'ai cloué sur le schéma dans ma réponse.

InformationsquelleAutor Mario Eis

J'ai fait un joli script complet que non seulement les scripts en dehors de toutes les autorisations, mais aussi tous les membres, et de mettre le glaçage sur le gâteau formats de sortie pour faciliter le copier/coller dans une nouvelle fenêtre de requête. J'ai posté le script pour mon blog et mettre à jour de temps en temps, mais est inférieure à la version actuelle, qui devrait couvrir la plupart des bases:

/********************************************************************
*                                                                  *
* Author: John Eisbrener                                           *
* Script Purpose: Script out Database Role Definition              *
* Notes: Please report any bugs to http://www.dbaeyes.com/        *
*                                                                  *
********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'
-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf
SELECT    @roleDesc = @roleDesc +
CASE dp.state
WHEN 'D' THEN 'DENY '
WHEN 'G' THEN 'GRANT '
WHEN 'R' THEN 'REVOKE '
WHEN 'W' THEN 'GRANT '
END + 
dp.permission_name + ' ' +
CASE dp.class
WHEN 0 THEN ''
WHEN 1 THEN --table or column subset on the table
CASE WHEN dp.major_id < 0 THEN
+ 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
ELSE
+ 'ON [' +
(SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
+ -- optionally concatenate column names
CASE WHEN MAX(dp.minor_id) > 0 
THEN '] ([' + REPLACE(
(SELECT name + '], [' 
FROM sys.columns 
WHERE object_id = dp.major_id 
AND column_id IN (SELECT minor_id 
FROM sys.database_permissions 
WHERE major_id = dp.major_id
AND USER_NAME(grantee_principal_id) IN (@roleName)
)
FOR XML PATH('')
) --replace final square bracket pair
+ '])', ', []', '')
ELSE ']'
END + ' '
END
WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
END COLLATE SQL_Latin1_General_CP1_CI_AS
+ 'TO [' + @roleName + ']' + 
CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM    sys.database_permissions dp
WHERE    USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class
SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf
-- Display users within Role.  Code stubbed by Joe Spivey
SELECT  @roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM    sys.database_principals users
INNER JOIN sys.database_role_members link 
ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles 
ON roles.principal_id = link.role_principal_id
WHERE   roles.name = @roleName
-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000
WHILE LEN(@roleDesc) > 8000
BEGIN
-- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))
PRINT LEFT(@roleDesc, @printCur)
SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END
PRINT @RoleDesc + 'GO'

De la note, vous pouvez exécuter dans une situation où le sp_AddRoleMember système sp ajoute utilisateur(s) de la DB qui n'était pas là. Dans ce cas, même si le ou les utilisateur(s) est ajoutée, ils ne sont PAS accordé l'autorisation CONNECT, et toute tentative de connexion faite par l'utilisateur ou le groupe peut générer une connexion de l'utilisateur en erreur. Pour corriger ce problème, vous devez exécuter par l'utilisateur/de groupe au sein de la db:

USE [DatabaseName]
GO
GRANT CONNECT TO [Login/GroupName]
GO

InformationsquelleAutor John Eisbrener

3

DANS SSMS en cliquant-droit sur l'utilisateur/login/rôle de nœud et en sélectionnant 'Script' sera le script de ce particulier de l'utilisateur /login /rôle. Vous ne pouvez pas le script rôle mebership cette manière.

Visual Studio avec la Base de données Drvelopment' option et Porte rouge SQL de Comparer peut générer le script de modification entre les bases de données, cela inclut les utilisateurs, les rôles et le rôle de l'adhésion.

Généré par VS l'appartenance au rôle ressemble à ceci:
```
EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'DOMAIN\User';
```
Si vous n'avez pas de VS, vous pouvez soit écrire ceux manuellement, ou de créer un script sql pour les générer.

Je suis sûr qu'il doit y avoir également un outil gratuit pour faire quelque chose comme cela, mais comme je n'ai pas besoin que j'ai de Visual Studio, je n'ai jamais cherché.

Modifier: je viens de réaliser que je suis la réponse à une mauvaise question, vous me demandez une autorisation de rôle et je peux vous dire à propos de l'appartenance de rôle. Désolé à ce sujet. Je vais quitter cette réponse ici dans le cas où il peut être utile à quelqu'un d'autre. Réponse par Alex Aza semble bon.
- quand je clic droit sur le rôle et sélectionnez "Script" Comme il génère simplement une ligne CREATE ROLE [RoleName] AUTHORIZATION [dbo] j'ai besoin d'obtenir les autorisations du rôle trop
- Geng: ouais, je sais. Personnellement, j'utilise Visual Studio pour ce type de personnel. Il generrates les goûts de EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'DOMAIN\User';
InformationsquelleAutor Andrew Savinykh
0

C'est une douleur, mais tout ce que vous cherchez est dans un couple de système de vues: sys.database_permissions, sys.database_principals, et sys.database_role_members. La raison pour laquelle il n'est pas un plaisir, car le major_id et minor_id dans sys.database_permissions ont des significations différentes en fonction de la catégorie de la colonne. Mais si vos droits sont relativement simples, alors ce pourrait ne pas être si mauvais. Donnez-lui un coup d'oeil et voir ce que vous pouvez obtenir.

InformationsquelleAutor Ben Thul

-- Use this if you have a lot of permissions assigned to a Database Role
-- Before running, set results to Text
SET NOCOUNT ON
Use MyDB;  -- CHANGE DATABASE NAME
DECLARE @RoleName varchar(50) = 'sp_exec' --- change role name here
SELECT 'CREATE ROLE [' + @RoleName + '];'+ char(13)
SELECT  'GRANT ' + prm.permission_name + ' ON [' + 
OBJECT_NAME(major_id) + '] TO [' + rol.name + '] ;' + char(13) COLLATE Latin1_General_CI_AS
from sys.database_permissions prm
join sys.database_principals rol on
prm.grantee_principal_id = rol.principal_id
where rol.name = @RoleName

Bienvenue à Débordement de Pile! Merci pour cet extrait de code, ce qui pourrait fournir quelques limitée, d'une aide immédiate. explication pourrait grandement améliorer sa valeur à long terme en montrant pourquoi est-ce une bonne solution au problème, et de le rendre plus utile pour les futurs lecteurs avec d'autres, des questions similaires. Merci de modifier votre réponse à ajouter quelques explications, y compris les hypothèses que vous avez faites.

InformationsquelleAutor MudassiR ShaikH

-1

J'ai ajouté un script supplémentaire de la fonctionnalité pour le code ci-dessus. L'sélectionnez scripts sur le rôle et les autorisations, donc vous avez besoin pour exécuter le résultat:

-- Update the RoleName with the name of your role
DECLARE @RoleName VARCHAR(75) = 'RoleName'
DECLARE @RoleTable TABLE ([GrantedBy] VARCHAR (50) NOT NULL, [Permission] VARCHAR (50) NOT NULL, [State] VARCHAR (50) NOT NULL)
DECLARE @RoleScript VARCHAR(75)
INSERT INTO @RoleTable SELECT p2.[name], dbp.[permission_name], dbp.[state_desc] 
FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
ON dbp.[grantor_principal_id] = p2.[principal_id]
WHERE p.[name] = @RoleName
SELECT 'USE [' +  DB_NAME() + '] CREATE ROLE [' + @RoleName + ']' AS 'Create Role'
SELECT 'USE [' +  DB_NAME() + '] GRANT ' + [Permission] + ' ON SCHEMA::[' + [GrantedBy] + '] TO [' + @RoleName + ']' AS 'Add Permissions' 
FROM @RoleTable

InformationsquelleAutor Tim Debisz

Vous devez vous connecter pour publier un commentaire.