Clone a SQL Server login with its permissions
Generate a script that clones a login, its roles, users and database permissions using five stored procedures.
Cloning a SQL Server login together with its server roles, server permissions, database users, role memberships and database permissions is a common task (new team member, replacement account). This article summarises the five-stored-procedure approach published on tidbytez.com; the full code is available on that page.
How it works
The procedures change nothing themselves: they generate a T-SQL script (a result set of statements) that you review and then run. They are deployed in a utility database (in the original script, the text DATABASE_PLACEHOLDER_NAME must be replaced with that database name).
| Procedure | Purpose |
|---|---|
CloneLogin |
Generates CREATE LOGIN (Windows or SQL), then copies the source login's server roles (sp_addsrvrolemember) and server permissions (server, logins, endpoints, etc.). |
CreateUserInDB |
Generates CREATE USER ... FROM LOGIN in every online database (or a single one) where the source login has a user. |
GrantUserRoleMembership |
Generates the sp_addrolemember calls that reproduce the source login's database roles. |
CloneDBPerms |
Generates database-level permissions: database, schemas, objects, columns, users, symmetric and asymmetric keys, certificates (with WITH GRANT OPTION where needed). |
CloneLoginAndAllDBPerms |
Master procedure that calls the other four and returns the complete script. |
Master procedure parameters
| Parameter | Description |
|---|---|
@NewLogin |
Name of the new login. |
@NewLoginPwd |
Password (only used for a SQL login). |
@WindowsLogin |
'T' for a Windows login, otherwise a SQL login with a password is generated. |
@LoginToClone |
Source login whose rights are copied. |
@DatabaseName |
Target database; NULL for all online databases. |
Usage example
EXEC [dbo].[CloneLoginAndAllDBPerms]
@NewLogin = new_app_user
,@NewLoginPwd = '<PASSWORD>'
,@WindowsLogin = N'F'
,@LoginToClone = 'existing_app_user'
,@DatabaseName = NULL;
The output is a list of statements such as the following, to review and then run:
USE [AppDb];
IF EXISTS (SELECT name FROM sys.database_principals WHERE name = 'existing_app_user')
BEGIN
CREATE USER [new_app_user] FROM LOGIN [new_app_user];
END;
Notes
- Review the generated script before running it, especially when cloning a login with elevated rights.
- For a Windows login,
@LoginToClonemust be the full name (DOMAIN\account) as shown insys.server_principals. - Never store the password in a versioned script.
- The original code belongs to its author: download it from the source above rather than copying it.