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
Microsoft SQL Server does not provide a built-in way to clone a login. However, it is possible to do so. There are two levels which we have to look at:
- Server role memberships and permissions
- Database role memberships and permissions
Both are lengthy to describe what to do, so we’ll tackle the server role memberships and permissions in this tip and cover the database role memberships and permissions in a second tip.
Even within the server role memberships and permissions, we do have a further breakdown of permissions. We have:
- The aforementioned server roles
- Permissions on the server itself
- Permissions on logins (server principals)
- Permissions on endpoints
A Quick Setup
We’ll need a login to clone, so let’s set up Bobby. I’ve built Bobby to cover every case we should encounter. While the example doesn’t include a DENY, which should be a last resort, the queries will work if the login has a DENY permission set for something.
CREATE LOGIN [Bobby] WITH PASSWORD = 'User$To!Clon3@';
GO
EXEC sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'securityadmin';
GO
EXEC sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'dbcreator';
GO
GRANT ALTER ANY SERVER ROLE TO [Bobby];
GRANT IMPERSONATE ON LOGIN::[sa] TO [Bobby];
GRANT CONTROL SERVER TO [Bobby];
GRANT ALTER ON ENDPOINT::[TSQL Default TCP] TO [Bobby];
GRANT ALTER ANY LOGIN TO [Bobby] WITH GRANT OPTION;
GRANT VIEW DEFINITION ON LOGIN::[sa] TO [Bobby];
GO
Warning: Please note that Bobby has permissions that you should think twice about granting, even in a development environment. He has CONTROL SERVER permission, which is the equivalent of being a member of the sysadmin role and he can impersonate the sa account. Also, Bobby is a member of securityadmin, which means he can escalate to sysadmin, even if he didn’t have any other permissions. Bobby has all of these permissions just to provide an example of the queries working. Don’t use Bobby as a real case!
Server Role Memberships
Let’s start with server role memberships. That’s easy to do. First, you’ll need to create the new login. When we put everything together into a stored procedure, we’ll have the stored procedure create the login as well, but for each of these individual steps, we’ll assume the login is already created so we can focus on getting the permissions correct.
With respect to server role memberships, there are two catalog views which we are interested in. They are:
- sys.server_role_memberships
- sys.server_principals
The sys.server_role_memberships catalog view joins together the logins with the roles found in sys.server_principals. Therefore, a query to get the correct permissions looks like this:
SET NOCOUNT ON;
DECLARE @NewLogin sysname;
DECLARE @LoginToClone sysname;
SET @NewLogin = 'Jimmy';
SET @LoginToClone = 'Bobby';
-- Query to handle server roles
SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + @NewLogin
+ ''', @rolename = ''' + R.name + ''';' AS 'SQL'
FROM sys.server_role_members AS RM
JOIN sys.server_principals AS L
ON RM.member_principal_id = L.principal_id
JOIN sys.server_principals AS R
ON RM.role_principal_id = R.principal_id
WHERE L.name = @LoginToClone;
I’m using variables here because we’re ultimately building towards a stored procedure to do it all. Note that we do have to join to the sys.server_principals catalog view twice: once to match up the login we’re trying to clone and the second time to get the name of the roles.
Also, this query builds T-SQL which uses sp_addsrvrolemember. This particular system stored procedure is considered deprecated and the recommended usage for SQL Server 2012 and above is to use ALTER ROLE. However, given that SQL Server 2005 is still in extended support as of the writing of this article (and SQL Server 2008/2008R2 for some time to come), I’ve decided to use the command that works across all currently supported versions. The syntax change to ALTER ROLE which allows adding memberships wasn’t added until SQL Server 2012, meaning it’s useless if you’re still managing SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2.
Server Permissions
Now we need to deal with server permissions. We’ll use sys.server_principals again, but we’ll also add:
- sys.endpoints
- sys.server_permissions
We’re dealing with three different types of objects: server, logins, and endpoints. As a result, we’ll need to put together three different queries. What I’ve done is combine the three different queries using UNION ALL. We can safely use UNION ALL because we know that there won’t be any duplicate roles. We’re making one large result set in anticipation of putting this together into a single stored procedure.
-- Query to handle server permissions
-- We need to consider server, server_principal (login), and endpoint
SELECT CASE P.state WHEN 'W' THEN
'GRANT ' + P.permission_name + ' TO [' + @NewLogin + '] WITH GRANT OPTION;'
ELSE
P.state_desc + ' ' + P.permission_name + ' TO [' + @NewLogin + '];'
END AS 'SQL'
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
WHERE L.name = @LoginToClone
AND P.class = 100
AND P.type <> 'COSQ'
UNION ALL
SELECT CASE P.state WHEN 'W' THEN
'GRANT ' + P.permission_name + ' ON LOGIN::[' + L2.name +
'] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
ELSE
P.state_desc + ' ' + P.permission_name + ' ON LOGIN::[' + L2.name
+ '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
END AS 'SQL'
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
JOIN sys.server_principals AS L2
ON P.major_id = L2.principal_id
WHERE L.name = @LoginToClone
AND P.class = 101
UNION ALL
SELECT CASE P.state WHEN 'W' THEN
'GRANT ' + P.permission_name + ' ON ENDPOINT::[' + E.name +
'] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
ELSE
P.state_desc + ' ' + P.permission_name + ' ON ENDPOINT::[' + E.name
+ '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
END AS 'SQL'
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
JOIN sys.endpoints AS E
ON P.major_id = E.endpoint_id
WHERE L.name = @LoginToClone
AND P.class = 105;
There are some things to note with each of these queries. First, the class identifies whether we’re dealing with server, login, or endpoint objects. You can find the values if you look at the sys.server_permissions topic in Books Online. We’ll use that to build our joins accordingly. Second, note that I’m intentionally not pulling back the case where type is equal to ‘COSQ’ because this is CONNECT SQL. Logins are granted this permission automatically when they are created. Now, if you have some logins defined where there is a DENY on this permission, please edit the query (and the subsequent stored procedure) accordingly to ensure this is included. Finally, I’m using COLLATE DATABASE_DEFAULT to force the proper collation when doing the string concatenation. If you perform string concatenation with strings of different collations, SQL Server will potentially throw an error because it doesn’t know what the correct collation should be for the combined string. We fix this issue by explicitly telling SQL Server what collation to use.
Putting It All Together
Note that the server permissions queries use the variable @LoginToClone as well. Therefore, let’s run both queries together. Since we’ve selected Bobby as the login to clone and Jimmy as the new login, here’s what the queries return. You can copy and paste these into a separate query window and set up Jimmy’s permissions.
A Better Way – The Stored Procedure
Instead of having to copy and paste manually, let’s automate all of this, to include the login creation. That leads to this stored procedure:
USE DBAWork;
GO
IF EXISTS(SELECT name FROM sys.procedures WHERE name = 'CloneLogin')
DROP PROCEDURE dbo.CloneLogin;
GO
CREATE PROCEDURE dbo.CloneLogin
@NewLogin sysname,
@NewLoginPwd NVARCHAR(MAX),
@WindowsLogin CHAR(1),
@LoginToClone sysname
AS BEGIN
SET NOCOUNT ON;
DECLARE @SQL nvarchar(MAX);
DECLARE @Return int;
IF (@WindowsLogin = 'T')
SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] FROM WINDOWS;'
ELSE
SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] WITH PASSWORD = N''' + @NewLoginPwd + ''';';
BEGIN TRAN;
PRINT @SQL;
EXEC @Return = sp_executesql @SQL;
IF (@Return <> 0)
BEGIN
ROLLBACK TRAN;
RAISERROR('Error encountered creating login', 16, 1);
RETURN(1);
END
-- Query to handle server roles
DECLARE cursRoleMemberSQL CURSOR FAST_FORWARD
FOR
SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + @NewLogin
+ ''', @rolename = ''' + R.name + ''';' AS 'SQL'
FROM sys.server_role_members AS RM
JOIN sys.server_principals AS L
ON RM.member_principal_id = L.principal_id
JOIN sys.server_principals AS R
ON RM.role_principal_id = R.principal_id
WHERE L.name = @LoginToClone;
OPEN cursRoleMemberSQL;
FETCH FROM cursRoleMemberSQL INTO @SQL;
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @SQL;
EXECUTE @Return = sp_executesql @SQL;
IF (@Return <> 0)
BEGIN
ROLLBACK TRAN;
RAISERROR('Error encountered assigning role memberships.', 16, 1);
CLOSE cursRoleMembersSQL;
DEALLOCATE cursRoleMembersSQL;
RETURN(1);
END
FETCH NEXT FROM cursRoleMemberSQL INTO @SQL;
END;
CLOSE cursRoleMemberSQL;
DEALLOCATE cursRoleMemberSQL;
DECLARE cursServerPermissionSQL CURSOR FAST_FORWARD
FOR
SELECT CASE P.state WHEN 'W' THEN
'USE master; GRANT ' + P.permission_name + ' TO [' + @NewLogin + '] WITH GRANT OPTION;'
ELSE
'USE master; ' + P.state_desc + ' ' + P.permission_name + ' TO [' + @NewLogin + '];'
END AS 'SQL'
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
WHERE L.name = @LoginToClone
AND P.class = 100
AND P.type <> 'COSQ'
UNION ALL
SELECT CASE P.state WHEN 'W' THEN
'USE master; GRANT ' + P.permission_name + ' ON LOGIN::[' + L2.name +
'] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
ELSE
'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON LOGIN::[' + L2.name
+ '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
END AS 'SQL'
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
JOIN sys.server_principals AS L2
ON P.major_id = L2.principal_id
WHERE L.name = @LoginToClone
AND P.class = 101
UNION ALL
SELECT CASE P.state WHEN 'W' THEN
'USE master; GRANT ' + P.permission_name + ' ON ENDPOINT::[' + E.name +
'] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
ELSE
'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON ENDPOINT::[' + E.name
+ '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
END AS 'SQL'
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
JOIN sys.endpoints AS E
ON P.major_id = E.endpoint_id
WHERE L.name = @LoginToClone
AND P.class = 105;
OPEN cursServerPermissionSQL;
FETCH FROM cursServerPermissionSQL INTO @SQL;
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @SQL;
EXEC @Return = sp_executesql @SQL;
IF (@Return <> 0)
BEGIN
ROLLBACK TRAN;
RAISERROR('Error encountered adding server level permissions', 16, 1);
CLOSE cursServerPermissionSQL;
DEALLOCATE cursServerPermissionSQL;
RETURN(1);
END
FETCH NEXT FROM cursServerPermissionSQL INTO @SQL;
END;
CLOSE cursServerPermissionSQL;
DEALLOCATE cursServerPermissionSQL;
COMMIT TRAN;
PRINT 'Login [' + @NewLogin + '] cloned successfully from [' + @LoginToClone + '].';
END;
Note that there are two more variables:
- @NewLoginPwd – The password to set for the new login (this won’t be used if the new login is a Windows login)
- @WindowsLogin – Whether or not the new login is a Windows login
Also, look carefully at how we’re executing each SQL statement. Here’s an example from the login creation:
EXEC @Return = sp_executesql @SQL;
If we get an error anywhere along the way, we want to rollback everything. Because the T-SQL commands are being written to output as they are being executed, we’ll know exactly where the stored procedure failed. Here’s how we rollback the login creation.
IF (@Return <> 0)
BEGIN
ROLLBACK TRAN;
RAISERROR('Error encountered creating login', 16, 1);
RETURN(1);
END
When dealing with the cursors, we also want to close them out and deallocate them. As a result, the rollback code includes those statements. Speaking of the cursors, take a good look at the part of the stored procedure that deals with server permissions. Note the use of “Use master;” as part of the batch query. We have to do this because server level permissions must be assigned in the master database. Including the USE statement forces the database context to change accordingly.
Why would we not be in the master database? Typically, I don’t like putting stored procedures in master if I can help it. I try to create a separate user database named Work, DBAWork, DBA, or something similar and put my “tool” stored procedures in that database. If you follow that practice, the database context for the stored procedure will be that user database. Hence, the USE command to force the context to the master database, which we need to be in for the server level permissions.
An example query using the stored procedure, to clone Jimmy from Bobby:
EXEC DBAWork.dbo.CloneLogin
@NewLogin = 'Jimmy',
@NewLoginPwd = 'SomeOtherPassw0rd!',
@LoginToClone = 'Bobby',
@WindowsLogin = 'F';
GO
If everything executes successfully, we’ll get a result like this:
If it doesn’t, we should see what the last statement attempted was, the error message SQL Server returned, and an indication from the stored procedure of where in the stored procedure we stopped.
For the purposes of this example, I simply ran the stored procedure twice, which generated the error creating the new login.
Next Steps
- Understand why the CONTROL SERVER permission is so powerful.
- Read up on how to audit server level permissions and roles in general.
- See how to capture all server role memberships for a recovery situation.
