LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon