Comment puis-je dupliquer ou cloner mes autorisations de connexion utilisateur RDS pour SQL Server ?

Solution

Remarque : Ce script n'apporte aucune modification aux nouvelles connexions à moins que l'utilisateur qui exécute le script n'ait accès à ces autorisations. Si l'utilisateur n'a pas accès aux autorisations nécessaires, celles-ci n'apparaissent pas dans le script. Cela est dû au fait que le login utilisé pour accéder au script ne dispose pas de privilèges d'affichage. En outre, si vous essayez d'ajouter le script d'autorisations manuellement alors que vous ne disposez pas de privilèges d'octroi, le script échoue.

1.    Copiez et exécutez le script MSSQL_RDS_Clone_Login pour créer une procédure stockée dans l'environnement dans lequel vous souhaitez dupliquer l'utilisateur. Vous utiliserez cette procédure stockée ultérieurement pour créer une copie des autorisations de connexion utilisateur, d'utilisateur de base de données, de niveau serveur et de base de données.

Vous pouvez créer la procédure stockée dans n'importe quelle base de données utilisateur ne faisant pas partie de la base de données système Amazon RDS pour SQL Server. Remplacez [DB_NAME] par le nom de la base de données utilisateur, en fonction de votre cas d'utilisation.

Le script suivant crée trois procédures stockées :

• DuplicateLogin : duplique l'identifiant et l'utilisateur de base de données dupliqué pour ces connexions dans chaque base de données contenant les autorisations de connexion à partir desquelles vous copiez.
• GrantUserRoleMembership : duplique les autorisations et les rôles des utilisateurs de la base de données pour le nouvel utilisateur.
• DuplicateRds : consolide les résultats des procédures stockées DuplicateLogin et GrantUserRoleMembership.

2.    Après avoir créé la procédure stockée, ouvrez une nouvelle fenêtre TSQL et exécutez la procédure stockée en utilisant le format suivant.

Remarque : Avant d'exécuter le script, assurez-vous que les résultats sont au format texte en appuyant sur CTRL+T sur votre clavier.

--SQL login
EXEC    DuplicateRDS @NewLogin=[<duplicate_login_name>]
         ,@NewLoginPwd = password
    ,@LoginToDuplicate = master_login
    , @WindowsLogin  = F
    ,@DatabaseName=NULL

-- Windows login
EXEC    DuplicateRDS @NewLogin=[<domain\duplicate_login_name>]
    ,@LoginToDuplicate = master_login
    ,@NewLoginPwd = NULL
    , @WindowsLogin  = T
    ,@DatabaseName=NULL

3.    Une fois le script généré, copiez-le depuis l'onglet Résultats et exécutez-le dans une nouvelle fenêtre de requête. Une fois le script exécuté, votre identifiant SQL Server est généré avec des autorisations au niveau du serveur et de la base de données similaires à celles de votre identifiant principal.

Remarque : Cet exemple exclut les autorisations SSISDB pour ssis_admin et ssis_logreader. Si vous avez besoin de ces autorisations, fournissez-les séparément.

ALTER ROLE [ssis_admin] ADD MEMBER [mydomain\user_name]
ALTER ROLE [ssis_logreader] ADD MEMBER [mydomain\user_name]
GO

Remarque : La sortie du script peut se produire de manière désordonnée en raison de l'utilisation de tables temporaires dans la procédure stockée. Si cela se produit, supprimez puis recréez la procédure stockée pour qu'elle prenne le bon format.

4.    Si vous avez des utilisateurs orphelins, exécutez le script suivant. Ce script vérifie la présence d'utilisateurs orphelins dans les bases de données pour lesquelles le script a échoué et supprime ces utilisateurs orphelins.

Le script suivant vérifie la présence d'utilisateurs orphelins :

Use [DB_NAME] ; 
GO 
exec sp_change_users_login @Action='Report' ;
GO

Le script suivant supprime les utilisateurs orphelins :

Use [DB_NAME] ;
GO
exec sp_revokedbaccess 'username'
GO

5.    Si vous ne souhaitez pas conserver les procédures stockées après avoir dupliqué la connexion (par exemple, pour des raisons de conformité), exécutez le script suivant :

USE [DB_NAME]
GO
DROP PROCEDURE [dbo].[DuplicateRDS]
GO
DROP PROCEDURE [dbo].[DuplicateLogin]
GO
DROP PROCEDURE [dbo].[GrantUserRoleMembership]
GO