Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Clone a SQL Server Login, Part 2 of 3


By:   |   Updated: 2015-05-29   |   Comments (1)   |   Related: More > Security

Problem

I need to create a login for my SQL Server and assign it the correct permissions. I already have another login which has the exact permissions I want. Is there a way I can clone the login that has all the permissions?

Solution

As mentioned in the first tip of this series, Microsoft SQL Server does not provide a built-in way to clone a login. However, SQL Server does provide all of the information we need to accomplish the cloning on our own. Since the first tip looked at server role memberships and permissions, this tip will focus on database role memberships and permissions.

When we consider a login at the database level, here are the considerations we have to undertake:

  • Whether or not the login has access to the database.
  • Creating the database user if the login to clone does have access to the database.
  • Database role memberships
  • Permissions on the database itself
  • Permissions on any schemas in the database
  • Permissions on individual objects

That last one is simple to say, but in the database there are a lot of different types of objects to consider. Therefore, the query we build to implement a copying of the permissions will be fairly lengthy. More on that in a bit.

A Quick Setup

This setup assumes you do have Bobby and Jimmy created and assigned the appropriate server level permissions and role memberships, as per the first tip. While the server permissions aren't necessary to demonstrate how to clone the permissions at the database level, the existence of the logins for Bobby and Jimmy are. Also, when we put everything together, including the work from the first tip, it'll be important that Bobby is set up properly. Also, the DBAWork database and the CloneLogin stored procedure are assumed to exist.

Here are the database specific setup commands:

USE master;
GO 

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'TestA')
  DROP DATABASE TestA;
  
CREATE DATABASE TestA;
GO 

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'TestB')
  DROP DATABASE TestB;
  
CREATE DATABASE TestB;
GO 

USE TestA;
GO 

CREATE USER [Bobby] FROM LOGIN [Bobby];
GO 

EXEC sp_addrolemember @rolename = 'db_securityadmin', @membername = 'Bobby';
GO 

CREATE ROLE TestRoleInTestA;
GO 

EXEC sp_addrolemember @rolename = 'TestRoleInTestA', @membername = 'Bobby';
GO 

GRANT SELECT ON SCHEMA::dbo TO [Bobby];
GO 

GRANT CREATE TABLE TO [Bobby];
GO 

GRANT CREATE PROCEDURE TO [Bobby] WITH GRANT OPTION;
GO 

USE TestB;
GO 

CREATE USER [Bobby] FROM LOGIN [Bobby];
GO 

GRANT IMPERSONATE ON USER::dbo TO [Bobby];
GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]';
GO 

CREATE ASYMMETRIC KEY ASymKey WITH ALGORITHM = RSA_2048;
GO 

GRANT CONTROL ON ASYMMETRIC KEY::ASymKey TO [Bobby];
GO 

CREATE CERTIFICATE TestCert 
WITH SUBJECT = 'A Test Cert to Show Permission Cloning';
GO 

GRANT VIEW DEFINITION ON CERTIFICATE::TestCert TO [Bobby];
GO 

CREATE SYMMETRIC KEY SymKey1 WITH ALGORITHM = AES_256 
ENCRYPTION BY ASYMMETRIC KEY ASymKey;
GO 

GRANT CONTROL ON SYMMETRIC KEY::SymKey1 TO [Bobby];
GO 

CREATE SYMMETRIC KEY SymKey2 WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE TestCert;
GO 

GRANT CONTROL ON SYMMETRIC KEY::SymKey2 TO [Bobby];
GO 

CREATE PROCEDURE dbo.SimpleProc
AS 
BEGIN
  SET NOCOUNT ON;

  SELECT 'Test Procedure';
END;
GO 

GRANT EXECUTE ON dbo.SimpleProc TO [Bobby];
GO 

There are a lot of permissions to grant because we have a lot of permissions to verify are cloned.

Checking to See If the Login to Clone Has Database Access

The first thing we'll need to do is check to see if the login we're cloning has database access. If it doesn't, there's no more work to do. After all, not every login has access to every database.

SET NOCOUNT ON;

DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
DECLARE @TSQL nvarchar(max);
DECLARE @Return int;

SET @LoginToClone = 'Bobby';
SET @NewLogin = 'Jimmy';

IF EXISTS(SELECT name FROM sys.database_principals WHERE name = @LoginToClone)
BEGIN
  SET @TSQL = 'CREATE USER [' + @NewLogin + '] FROM LOGIN [' + @NewLogin + '];';
  EXEC @Return = sp_executesql @TSQL;
END;

You'll want to run this in both databases. As of right now, the script doesn't tell you much. It'll run and you'll get a successful result. We'll add more to it when we turn it into a stored procedure.

Successful run of create user script.

However, if you check the users folder under the database, you'll see that Jimmy was created:

Jimmy is created in TestA DB.

Database Role Memberships

Of all the things we need to do to clone database permissions, handling database role memberships is the easiest part.

In our database setup, we only have database role memberships defined in the TestA database. So you'll only need to run the script there. In the first tip, we merely wrote out the appropriate commands to handle the role memberships. That is, until we built the final stored procedure. Given that the joins are similar, we're going to go ahead and build the execution as well:

SET NOCOUNT ON;

DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
DECLARE @TSQL nvarchar(max);
DECLARE @Return int;

SET @LoginToClone = 'Bobby';
SET @NewLogin = 'Jimmy';

DECLARE cursDBRoleMembersSQL CURSOR FAST_FORWARD
FOR
SELECT 'EXEC sp_addrolemember @rolename = ''' + r.name 
  + ''', @membername = ''' + @NewLogin + ''';'
FROM sys.database_principals AS U
  JOIN sys.database_role_members AS RM
    ON U.principal_id = RM.member_principal_id
  JOIN sys.database_principals AS R
    ON RM.role_principal_id = R.principal_id
WHERE U.name = @LoginToClone;

OPEN cursDBRoleMembersSQL;

FETCH FROM cursDBRoleMembersSQL INTO @TSQL;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  PRINT @TSQL;
  EXECUTE @Return = sp_executesql @TSQL;

  IF (@RETURN <> 0)
  BEGIN
    ROLLBACK TRAN;
	RAISERROR('Error encountered assigning DB role memberships.', 16, 1);
	-- When we roll this into the stored procedure, we'll also add
	-- CLOSE and deallocate commands for the cursor as well as a return.
  END;

  FETCH NEXT FROM cursDBRoleMembersSQL INTO @TSQL;
END;

CLOSE cursDBRoleMembersSQL;
DEALLOCATE cursDBRoleMembersSQL;

And when you execute the script and check on Jimmy, you'll see that two T-SQL commands should have been specified and executed:

Execution of T-SQL to add role memberships

Checking visually, we can see Jimmy is a member of the two roles which Bobby is a member of as well:

Visual Check of DB role memberships

Database Permissions

Like with server permissions, there are a lot of objects we need to handle in order to clone all the possible permissions. They are:

  • Permissions at the database level
  • Permissions on schema
  • Permissions on objects contained within a schema
  • Permissions on users
  • Permission on the three types of encryption objects: asymmetric keys, certificates, and symmetric keys

This does mean I'm leaving out some things: assemblies, service broker pieces, data types, and full text catalogs among them. You can easily extend the script to include those things using UNION ALL if you are using them in your environment. As it is, the SQL code is rather long:

SET NOCOUNT ON;

DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
DECLARE @TSQL nvarchar(max);
DECLARE @Return int;

SET @LoginToClone = 'Bobby';
SET @NewLogin = 'Jimmy';

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON DATABASE::[' 
     + DB_NAME() + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON DATABASE::[' 
     + DB_NAME() + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
WHERE class = 0
  AND P.[type] <> 'CO'
  AND U.name = @LoginToClone
  
UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON SCHEMA::[' 
     + S.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON SCHEMA::[' 
     + S.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.schemas AS S
    ON S.schema_id = P.major_id
WHERE class = 3
  AND U.name = @LoginToClone
  
UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.objects AS O
    ON O.object_id = P.major_id
WHERE class = 1
  AND U.name = @LoginToClone
  AND P.major_id > 0
  AND P.minor_id = 0

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] (' + C.name + ') TO [' + @NewLogin + '] WITH GRANT OPTION;' 
	 COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON OBJECT::[' 
     + O.name + '] (' + C.name + ') TO [' + @NewLogin + '];' 
	 COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.objects AS O
    ON O.object_id = P.major_id
  JOIN sys.columns AS C
    ON C.column_id = P.minor_id AND o.object_id = C.object_id
WHERE class = 1
  AND U.name = @LoginToClone
  AND P.major_id > 0
  AND P.minor_id > 0

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON USER::[' 
     + U2.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON USER::[' 
     + U2.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.database_principals AS U2
    ON U2.principal_id = P.major_id
WHERE class = 4
  AND U.name = @LoginToClone

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON SYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON SYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.symmetric_keys AS K
    ON P.major_id = K.symmetric_key_id
WHERE class = 24
  AND U.name = @LoginToClone

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON ASYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON ASYMMETRIC KEY::[' 
     + K.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.asymmetric_keys AS K
    ON P.major_id = K.asymmetric_key_id
WHERE class = 26
  AND U.name = @LoginToClone

UNION ALL

SELECT CASE [state]
   WHEN 'W' THEN 'GRANT ' + permission_name + ' ON CERTIFICATE::[' 
     + C.name + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
   ELSE state_desc + ' ' + permission_name + ' ON CERTIFICATE::[' 
     + C.name + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
   END AS 'Permission'
FROM sys.database_permissions AS P
  JOIN sys.database_principals AS U
    ON P.grantee_principal_id = U.principal_id
  JOIN sys.certificates AS C
    ON P.major_id = C.certificate_id
WHERE class = 25
  AND U.name = @LoginToClone;

If we run this against the TestA database, we'll get the following results back:

Permissions for Jimmy against TestA DB

And here are the results for the TestB database:

Permissions for Jimmy against TestB DB

Building a Stored Procedure

While we could have the script saved off and pull it up each time we needed to clone database permissions, what would be better is if we already have it in a stored procedure. In actuality, doing this as a stored procedure means we can simplify the SELECT statement and not have to rely on all the UNION ALLs to build the full list of permissions.

Instead, what we'll do is gather the permissions for each type of object and then make the permissions change. This does mean the use of several cursors, but this is a row-by-row operations. Here's the script to build the stored procedure:

USE DBAWork;
GO 

CREATE PROC dbo.CloneDBPerms
  @NewLogin sysname,
  @LoginToClone sysname,
  @DBName sysname
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL nvarchar(max);
	DECLARE @Return int;

	CREATE TABLE #DBPermissionsTSQL 
	(
		PermsTSQL nvarchar(MAX)
	);


	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON DATABASE::[' 
		 + @DBName + '] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON DATABASE::[' 
		 + @DBNAME + '] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	WHERE class = 0
	  AND P.[type] <> ''CO''
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.schemas AS S
		ON S.schema_id = P.major_id
	WHERE class = 3
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.objects AS O
		ON O.object_id = P.major_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id = 0';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '] WITH GRANT OPTION;'' 
		 COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '];'' 
		 COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.objects AS O
		ON O.object_id = P.major_id
	  JOIN [' + @DBName + '].sys.columns AS C
		ON C.column_id = P.minor_id AND o.object_id = C.object_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id > 0;'
	
	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.database_principals AS U2
		ON U2.principal_id = P.major_id
	WHERE class = 4
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.symmetric_keys AS K
		ON P.major_id = K.symmetric_key_id
	WHERE class = 24
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.asymmetric_keys AS K
		ON P.major_id = K.asymmetric_key_id
	WHERE class = 26
	  AND U.name = ''' + @LoginToClone + ''';';
	
	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE state_desc + '' '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DBName + '].sys.database_permissions AS P
	  JOIN [' + @DBName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DBName + '].sys.certificates AS C
		ON P.major_id = C.certificate_id
	WHERE class = 25
	  AND U.name = ''' + @LoginToClone + ''';';

	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		ROLLBACK TRAN;
		RAISERROR('Error encountered building permissions.', 16, 1);
		RETURN(1);
	END

	DECLARE cursDBPermsSQL CURSOR FAST_FORWARD
	FOR
	SELECT PermsTSQL FROM #DBPermissionsTSQL

	OPEN cursDBPermsSQL;

	FETCH FROM cursDBPermsSQL INTO @SQL;

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	  SET @SQL = 'USE [' + @DBName + ']; ' + @SQL;

	  PRINT @SQL;
	  EXEC @Return = sp_executesql @SQL;

	  IF (@Return <> 0)
	  BEGIN
		  ROLLBACK TRAN;
		  RAISERROR('Error granting permission', 16, 1);
		  CLOSE cursDBPermsSQL;
		  DEALLOCATE cursDBPermsSQL;
		  RETURN(1);
	  END;

	  FETCH NEXT FROM cursDBPermsSQL INTO @SQL;
	END;

	CLOSE cursDBPermsSQL;
	DEALLOCATE cursDBPermsSQL;
	DROP TABLE #DBPermissionsTSQL;
END;
GO 

Next Steps


Last Updated: 2015-05-29


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 01, 2019 - 3:39:00 PM - Vlad Ignatov Back To Top

Brian, This is good stuff!  Really helped me in cloning some logins.  I did however find a small issue related to schemas.  Object-level permissions should account for schema names other than the default.  I propose modifications to the folowing blocks of code to include the actual schema name:

SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL) 
SELECT CASE [state]
   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' 
 + O.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
   ELSE state_desc + '' '' + permission_name + '' ON OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' 
 + O.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
   END AS ''Permission''
FROM [' + @DBName + '].sys.database_permissions AS P
  JOIN [' + @DBName + '].sys.database_principals AS U
ON P.grantee_principal_id = U.principal_id
  JOIN [' + @DBName + '].sys.objects AS O
ON O.object_id = P.major_id
WHERE class = 1
  AND U.name = ''' + @LoginToClone + '''
  AND P.major_id > 0
  AND P.minor_id = 0';

-- and this one .....

SET @SQL = 'INSERT INTO #DBPermissionsTSQL (PermsTSQL)
SELECT CASE [state]
   WHEN ''W'' THEN ''GRANT '' + permission_name + '' ON OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' 
 + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '] WITH GRANT OPTION;'' 
 COLLATE DATABASE_DEFAULT
   ELSE state_desc + '' '' + permission_name + '' ON OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' 
 + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '];'' 
 COLLATE DATABASE_DEFAULT
   END AS ''Permission''
FROM [' + @DBName + '].sys.database_permissions AS P
  JOIN [' + @DBName + '].sys.database_principals AS U
ON P.grantee_principal_id = U.principal_id
  JOIN [' + @DBName + '].sys.objects AS O
ON O.object_id = P.major_id
  JOIN [' + @DBName + '].sys.columns AS C
ON C.column_id = P.minor_id AND o.object_id = C.object_id
WHERE class = 1
  AND U.name = ''' + @LoginToClone + '''
  AND P.major_id > 0
  AND P.minor_id > 0;'

Learn more about SQL Server tools