Cloning a SQL Server Login with all permissions using PowerShell
By: Jeffrey Yao | Comments (2) | Related: > Security
Problem
It's a common task that a DBA is asked to grant a new hire the same privileges as another colleague's account. This can become tedious and time-consuming if this source account exists in multiple databases on multiple servers for different applications, is there an easier way to do this work?
In a similar context, during auditing time, a DBA may be asked to provide a user's account privilege across all production servers? Is there any way to do this?
Solution
If you Google for sql login clone or something similar, most likely you will find T-SQL solutions (some of which are listed in the Next Steps section), there are a few minor issues with the T-SQL approach:
- We need to install these scripts on the target servers (to generate the needed stored procedures) and then run a stored procedure on each target server.
- We cannot automatically create the generated T-SQL into a .sql file for auditing purposes.
- Heavy use of the dynamic T-SQL code makes the solution lengthy and inconvenient for reading and maintenance of the script.
This tips proposes a better solution based on PowerShell and SMO to address all the issues mentioned above.
New PowerShell Approach Algorithm
- In the cmdlet function, we will accept a list of SQL Server instance names and a login name ($OldLogin) whose permissions will be cloned.
- For each instance, use SMO Server.EnumObjectPermissions(loginName) to list server objects (such as EndPoint, login) permissions and Server.EnumServerPermissions(loginName) to list Server level permissions for $OldLogin.
- Use Login.EnumDatabaseMappings() to find each database where a database account exists that maps to this $OldLogin account.
- In each dataabase for the mapped user, we will go through Database.EnumDatabasePermissions , Database.EnumObjectPermissions, User.EnumRoles, and EnumObjectPermissions for user's Certificate, AsymmetricKeys, SymmetricKeys, ServiceBrokers, etc. to retrieve each permission of the user.
- All the retrieved permission info will be added into a hashtable array. Later, we will loop through this array to export a permission script to a file or to run the script for cloning a new login account with the same permissions.
Test Environment Preparation
Here I will borrow the script from this excellent article series and will add/modify quite a few new items such as adding GRANT SELECT on columns of a table, adding a DENY permission or adding permissions to service brokers, fulltext catalog, etc.
-- setup.sql to set up test environment
-- 1st: Set up login account and assign a few permissions plus role memberships
--setup.sql to set up test environment
-- 1st: Set up login account and assign a few permissions plus role memberships
USE master;
GO
if exists (select * from sys.server_principals where name = 'Bobby')
drop login [Bobby];
CREATE LOGIN [Bobby] WITH PASSWORD = 'User$To!Clon3@';
GO
EXEC sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'securityadmin';
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
-- 2nd. Create databases
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
-- 3rd, create permissions or db role memberships for [Bobby]
USE TestA;
GO
CREATE USER [Bobby] FROM LOGIN [Bobby];
GO
EXEC sp_addrolemember @rolename = 'db_securityadmin', @membername = 'Bobby';
CREATE ROLE TestRoleInTestA;
GO
EXEC sp_addrolemember @rolename = 'TestRoleInTestA', @membername = 'Bobby';
GO
if object_id('dbo.t', 'U') is not null
drop table dbo.t;
create table dbo.t (a int identity, b varchar(30), d datetime default current_timestamp);
go
-- only SELECT ON TWO columns
GRANT SELECT on object::dbo.t (a, d) to [Bobby];
DENY UPDATE on object::dbo.t to [Bobby];
GRANT SELECT ON SCHEMA::dbo TO [Bobby];
GRANT CREATE TABLE TO [Bobby];
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 = 'S0m3Str0ng!!P4ssw0rd@';
CREATE ASYMMETRIC KEY ASymKey WITH ALGORITHM = RSA_2048;
CREATE SYMMETRIC KEY SymKey1 WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY ASymKey;
CREATE CERTIFICATE TestCert
WITH SUBJECT = 'A Test Cert to Show Permission Cloning';
CREATE SYMMETRIC KEY SymKey2 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;
GO
CREATE PROCEDURE dbo.SimpleProc
AS
BEGIN
SET NOCOUNT ON;
SELECT 'Test Procedure';
END;
GO
GRANT CONTROL ON ASYMMETRIC KEY::ASymKey TO [Bobby];
GRANT VIEW DEFINITION ON CERTIFICATE::TestCert TO [Bobby];
GRANT CONTROL ON SYMMETRIC KEY::SymKey1 TO [Bobby];
GRANT CONTROL ON SYMMETRIC KEY::SymKey2 TO [Bobby];
GRANT EXECUTE ON dbo.SimpleProc TO [Bobby];
DENY VIEW DEFINITION ON dbo.SimpleProc TO [Bobby];
GO
Use testB
go
CREATE XML SCHEMA COLLECTION XSC AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
xmlns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:complexType name="StepType" mixed="true" >
<xsd:choice minOccurs="0" maxOccurs="unbounded" >
<xsd:element name="tool" type="xsd:string" />
<xsd:element name="material" type="xsd:string" />
<xsd:element name="blueprint" type="xsd:string" />
<xsd:element name="specs" type="xsd:string" />
<xsd:element name="diag" type="xsd:string" />
</xsd:choice>
</xsd:complexType>
<xsd:element name="root">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="Location" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />
</xsd:sequence>
<xsd:attribute name="LocationID" type="xsd:integer" use="required"/>
<xsd:attribute name="SetupHours" type="xsd:decimal" use="optional"/>
<xsd:attribute name="MachineHours" type="xsd:decimal" use="optional"/>
<xsd:attribute name="LaborHours" type="xsd:decimal" use="optional"/>
<xsd:attribute name="LotSize" type="xsd:decimal" use="optional"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
GRANT ALTER ON XML SCHEMA COLLECTION::dbo.XSC TO [BOBBY];
DENY TAKE OWNERSHIP ON XML SCHEMA COLLECTION::dbo.XSC TO [BOBBY];
GO
alter database testA set enable_broker;
use testA
create message type [//MyTest/Sample/RequestMsg] validation = well_formed_xml;
create message type [//MyTest/Sample/ReplyMsg] validation = well_formed_xml;
create contract [//Mytest/Sample/MyContract] (
[//MyTest/Sample/RequestMsg] sent by initiator,
[//MyTest/Sample/ReplyMsg] sent by target);
create queue InitQu;
--create queue TargetQu;
create service [//MyTest/Sample/InitSvc] on queue InitQu;
create route ExpenseRoute with service_name= '//MyTest/Sample/InitSvc', Address='tcp://www.sqlserver.com:1234';
grant alter on Contract::[//Mytest/Sample/MyContract] to [Bobby]
Grant references on message type::[//MyTest/Sample/ReplyMsg] to [Bobby]
Deny view definition on Route::ExpenseRoute to [Bobby]
Grant alter on route::ExpenseRoute to [Bobby]
Grant View Definition on Service::[//MyTest/Sample/InitSvc] to [Bobby]
Deny alter on Service::[//MyTest/Sample/InitSvc] to [Bobby]
create fulltext catalog ftCat as default;
create fulltext stoplist mystopList;
grant alter on fulltext catalog::ftcat to [Bobby]
Deny view definition on fulltext Stoplist::myStopList to [Bobby]
grant alter on fulltext Stoplist::myStopList to [Bobby]
go
USE master
GRANT VIEW SERVER STATE TO [bobby];
In this environment, we put lots of different grant/deny permissions for account [Bobby] either as a server login account or database account.
In summary (after removing other non-permission related stuff), here is the permission grant/deny SQL script.
-- summary script -- as server Login account use Master; EXEC sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'securityadmin'; 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]; GRANT VIEW SERVER STATE TO [bobby]; GO -- as db account in [TestA] db Use TestA EXEC sp_addrolemember @rolename = 'db_securityadmin', @membername = 'Bobby'; EXEC sp_addrolemember @rolename = 'TestRoleInTestA', @membername = 'Bobby'; GRANT SELECT on object::dbo.t (a, d) to [Bobby]; DENY UPDATE on object::dbo.t to [Bobby]; GRANT SELECT ON SCHEMA::dbo TO [Bobby]; GRANT CREATE TABLE TO [Bobby]; GRANT CREATE PROCEDURE TO [Bobby] WITH GRANT OPTION; GRANT ALTER ON Contract::[//Mytest/Sample/MyContract] to [Bobby] GRANT REFERENCES ON MESSAGE TYPE::[//MyTest/Sample/ReplyMsg] to [Bobby] DENY VIEW DEFINITION on Route::ExpenseRoute to [Bobby] GRANT ALTER ON ROUTE::ExpenseRoute to [Bobby] Grant View Definition on Service::[//MyTest/Sample/InitSvc] to [Bobby] DENY ALTER ON Service::[//MyTest/Sample/InitSvc] to [Bobby] GO -- as db account in [TestB] db use TestB EXEC sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'securityadmin'; EXEC sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'dbcreator'; 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]; GRANT ALTER ON XML SCHEMA COLLECTION::dbo.XSC TO [BOBBY]; DENY TAKE OWNERSHIP ON XML SCHEMA COLLECTION::dbo.XSC TO [BOBBY]; GO
On my local computer, I have two SQL instances, one is called [TP_W520] (the default instance) and another [TP_W520\SQL2014]. I have the script setup to run on both instances.
OK, here is the PowerShell script.
<#
.Synopsis
Clone a SQL Server Login based on another exsiting login.
.DESCRIPTION
Clone a SQL Server Login based on another exsiting login on one or multiple servers and the process can generate a script for auditing purpose.
.EXAMPLE
The following command scripts out the permissions of login account [John] and generates the script at "c:\temp\clone.sql"
Notice, parameters [OldLogin] and [NewLogin] uses the same value of "John"
Clone-SQLLogin -Server Server1, Server2 -OldLogin John -NewLogin John -FilePath "c:\temp\clone.sql"
.EXAMPLE
The following command exports a script that can be used to clone login account [John] for new login account [David], and the script is created at "c:\temp\clone.sql"
also the -Execute parameter means the new account "David" will be created
Clone-SQLLogin -Server Server1, Server2 -OldLogin John -NewLogin David -NewPassword 'P@$$W0rd' -FilePath "c:\temp\clone.sql" -Execute;
.Parameter ServerInstance
ServerInstance is of string array datat ype, and can accept a string of sql instance names, spearated by comma. (mandatory)
.Parameter OldLogin
The login account is the source where all the permissions will be scripted out and to be used for the NewLogin account (mandatory)
.Parameter NewLogin
The login account is the target account that we want to clone (mandatory)
.Parameter NewPassword
The password for the new login account if we want to create the login, default to empty string "" (optional)
.Parameter FilePath
The full path name for the generated sql script (optional)
.Parameter Execute
This is a swich parameter, if present, it means we need to create the NewLogin account.
.OUTPUTS
none
.NOTES
A few service broker related permissions are not covered in this version 1.
#>
#requires -version 3.0
add-type -assembly "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; #if Version-11.xx means sql server 2012
function Clone-SQLLogin
{
[CmdletBinding(SupportsShouldProcess=$true)]
Param
(
# Param1 help description
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
Position=0)]
[string[]] $ServerInstance,
[Parameter(Mandatory=$true)]
[string] $OldLogin,
[Parameter(Mandatory=$true)]
[string] $NewLogin,
[string] $NewPassword="",
[string] $FilePath="",
[switch] $Execute
)
Begin
{
[string]$newUser=$newLogin.Substring($newLogin.IndexOf('\')+1); # if $newLogin is a Windows account, such as domain\username, since "\" is invalid in db user name, we need to remove it
[hashtable[]] $hta = @(); # a hashtable array
[hashtable] $h = @{};
if ( ($FilePath -ne "") -and (test-path -Path $FilePath))
{ del -Path $filepath; }
}
Process
{
foreach ($sqlinstance in $ServerInstance)
{
$svr = new-object "Microsoft.SqlServer.Management.Smo.Server" $sqlinstance;
if ($svr.Edition -eq $null)
{
Write-warning "$sqlinstance cannot be connected";
continue;
}
[string]$str = "";
if (-not $WindowsLogin)
{
$str += "create login $($newLogin) with password='$($newPassword)'; `r`n"
}
else
{
$str += "create login $($newLogin) from windows;`r`n "
}
#find role membership for $login
if ($svr.logins[$OldLogin] -ne $null)
{ $svr.logins[$oldLogin].ListMembers() | % {$str += "exec sp_addsrvrolemember @loginame = '$($newLogin)', @rolename = '$($_)'; `r`n"};}
else
{ Write-warning "$oldLogin does not exist on server [$($svr.name)] so this sql instance is skipped"; continue; }
# find permission granted to $login
$svr.EnumObjectPermissions($oldLogin) | % { if ($_.PermissionState -eq 'GrantWithGrant')
{$str += "GRANT $($_.PermissionType) on $($_.ObjectClass)::[$($_.ObjectName)] to [$newLogin] WITH GRANT OPTION; `r`n"}
else
{ $str += "$($_.PermissionState) $($_.PermissionType) on $($_.ObjectClass)::[$($_.ObjectName)] to [$newLogin]; `r`n"} }
$svr.EnumServerPermissions($oldLogin) | % { if ($_.PermissionState -eq 'GrantWithGrant')
{ $str += "GRANT $($_.PermissionType) to [$newLogin] WITH GRANT OPTION; `r`n"}
else
{ $str += "$($_.PermissionState) $($_.PermissionType) to [$newLogin]; `r`n" } }
$h = @{Server=$sqlinstance; DBName = 'master'; sqlcmd = $str};
$hta += $h;
#$str;
$ObjPerms = @(); # store login mapped users in each db on $svr
$Roles = @();
$DBPerms = @();
foreach ($itm in $svr.logins[$oldLogin].EnumDatabaseMappings())
{
if ($svr.Databases[$itm.DBName].Status -ne 'Normal')
{ continue;}
if ($svr.Databases[$itm.DBName].Users[$newUser] -eq $null)
{ $hta += @{Server=$sqlinstance; DBName = $itm.DBName; sqlcmd = "create user [$newUser] for login [$newLogin];`r`n" }; }
$r = $svr.Databases[$itm.DBName].Users[$itm.UserName].EnumRoles();
if ($r -ne $null)
{
$r | % { $hta += @{Server=$sqlinstance; DBName = $itm.DBName; sqlcmd = "exec sp_addrolemember @rolename='$_', @memberName='$($newUser)';`r`n" } }
}
$p = $svr.Databases[$itm.DBName].EnumDatabasePermissions($itm.UserName);
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
$p = $svr.Databases[$itm.DBName].EnumObjectPermissions($itm.UserName)
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p}; }
$p = $svr.Databases[$itm.DBName].Certificates | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
#AsymmetricKeys
$p = $svr.Databases[$itm.DBName].AsymmetricKeys | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p}; }
#SymmetricKeys
$p = $svr.Databases[$itm.DBName].SymmetricKeys | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
#XMLSchemaCollections
$p = $svr.Databases[$itm.DBName].XMLSchemaCollections | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
#service broker components
$p = $svr.Databases[$itm.DBName].ServiceBroker.MessageTypes | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
$p = $svr.Databases[$itm.DBName].ServiceBroker.Routes | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
$p = $svr.Databases[$itm.DBName].ServiceBroker.ServiceContracts | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
$p = $svr.Databases[$itm.DBName].ServiceBroker.Services | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
#Full text
$p = $svr.Databases[$itm.DBName].FullTextCatalogs | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
$p = $svr.Databases[$itm.DBName].FullTextStopLists | % {$_.EnumObjectPermissions($itm.UserName)}
if ($p -ne $null)
{ $ObjPerms += @{DBName=$itm.DBName; Permission=$p};}
}
#generate t-sql to apply permission using SMO only
#[string]$str = ([System.String]::Empty)
foreach ($pr in $ObjPerms)
{
$h = @{Server=$sqlinstance; DBName=$($pr.DBName); sqlcmd=""};
$str = "" #"use $($pr.DBName) `r`n"
foreach ($p in $pr.Permission)
{
[string]$op_state = $p.PermissionState;
if ($p.ObjectClass -ne "ObjectOrColumn")
{
[string] $schema = "";
if ($p.ObjectSchema -ne $null)
{ $schema = "$($p.ObjectSchema)."}
[string]$option = "";
if ($op_state -eq "GRANTwithGrant")
{
$op_state = 'GRANT';
$option = ' WITH GRANT OPTION';
}
Switch ($p.ObjectClass)
{
'Database' { $str += "$op_state $($p.PermissionType) to [$newUser]$option;`r`n";}
'SqlAssembly' { $str += "$op_state $($p.PermissionType) ON Assembly::$($schema)$($p.ObjectName) to [$newUser]$option;`r`n";}
'Schema' { $str += "$op_state $($p.PermissionType) ON SCHEMA::$($schema)$($p.ObjectName) to [$newUser]$option;`r`n";}
'UserDefinedType' { $str += "$op_state $($p.PermissionType) ON TYPE::$($schema)$($p.ObjectName) to [$newUser]$option;`r`n";}
'AsymmetricKey' { $str += "$op_state $($p.PermissionType) ON ASYMMETRIC KEY::$($schema)$($p.ObjectName) to [$newUser]$option;`r`n";}
'SymmetricKey' { $str += "$op_state $($p.PermissionType) ON SYMMETRIC KEY::$($schema)$($p.ObjectName) to [$newUser]$option;`r`n";}
'Certificate' { $str += "$op_state $($p.PermissionType) ON Certificate::$($schema)$($p.ObjectName) to [$newUser]$option`r`n";}
'XmlNamespace' { $str += "$op_state $($p.PermissionType) ON XML SCHEMA COLLECTION::$($schema)$($p.ObjectName) to [$newUser]$option`r`n";}
'FullTextCatalog' { $str += "$op_state $($p.PermissionType) ON FullText Catalog::$($schema)[$($p.ObjectName)] to [$newUser]$option`r`n";}
'FullTextStopList' { $str += "$op_state $($p.PermissionType) ON FullText Stoplist::$($schema)[$($p.ObjectName)] to [$newUser]$option`r`n";}
'MessageType' { $str += "$op_state $($p.PermissionType) ON Message Type::$($schema)[$($p.ObjectName)] to [$newUser]$option`r`n";}
'ServiceContract' { $str += "$op_state $($p.PermissionType) ON Contract::$($schema)[$($p.ObjectName)] to [$newUser]$option`r`n";}
'ServiceRoute' { $str += "$op_state $($p.PermissionType) ON Route::$($schema)[$($p.ObjectName)] to [$newUser]$option`r`n";}
'Service' { $str += "$op_state $($p.PermissionType) ON Service::$($schema)[$($p.ObjectName)] to [$newUser]$option`r`n";}
#you can add other stuff like Available Group etc in this switch block as well
}#switch
}
else
{
[string]$col = "" #if grant is on column level, we need to capture it
if ($p.ColumnName -ne $null)
{ $col = "($($p.ColumnName))"};
$str += "$op_state $($p.PermissionType) ON Object::$($p.ObjectSchema).$($p.ObjectName) $col to [$newUser];`r`n";
}#else
}
#$str += "go`r`n";
$h.sqlcmd = $str;
$hta += $h;
}
}#loop $ServerInstance
} #process block
End
{
[string] $sqlcmd = "";
if ($FilePath.Length -gt 3) # $FilePath is provided
{
[string]$servername="";
foreach ($h in $hta)
{
if ($h.Server -ne $Servername)
{
$ServerName=$h.Server;
$sqlcmd += ":connect $servername `r`n"
}
$sqlcmd += "use $($h.DBName);`r`n" + $h.sqlcmd +"`r`ngo`r`n";
}
$sqlcmd | out-file -FilePath $FilePath -Append ;
}
if ($Execute)
{
foreach ($h in $hta)
{
$server = new-object "Microsoft.sqlserver.management.smo.server" $h.Server;
$database = $server.databases[$h.DBName];
$database.ExecuteNonQuery($h.sqlcmd)
}
} #$Execute
}#end block
} #clone-sqllogin
# test, change parameters to your own. The following creates a script about all permissions assigned to [Bobby]
# Clone-SQLLogin -Server "$env:ComputerName", "$env:ComputerName\sql2014" -OldLogin Bobby -NewLogin Bobby -FilePath "c:\temp\Bobby_perm.sql";
Start Test
Start a PowerShell ISE window, copy & paste the PowerShell script to a new window and uncomment the last line (making necessary changes, such as -Server parameter values) and run the script.
You will see a newly generated script at c:\temp\Bobby_perm.sql (assuming your have c:\temp\ folder)
After opening this c:\temp\Bobby_perm.sql in Notepad, I can see the following
:connect TP_W520 use master; create login Bobby with password=''; exec sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'securityadmin'; exec sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'dbcreator'; Grant IMPERSONATE on Login::[sa] to [Bobby]; Grant VIEW DEFINITION on Login::[sa] to [Bobby]; Grant ALTER on Endpoint::[TSQL Default TCP] to [Bobby]; GRANT ALTER ANY LOGIN to [Bobby] WITH GRANT OPTION; Grant ALTER ANY SERVER ROLE to [Bobby]; Grant CONTROL SERVER to [Bobby]; Grant CONNECT SQL to [Bobby]; Grant VIEW SERVER STATE to [Bobby]; go use TestA; exec sp_addrolemember @rolename='TestRoleInTestA', @memberName='Bobby'; go use TestA; exec sp_addrolemember @rolename='db_securityadmin', @memberName='Bobby'; go use TestA; Grant CONNECT to [Bobby]; GRANT CREATE PROCEDURE to [Bobby] WITH GRANT OPTION; Grant CREATE TABLE to [Bobby]; go use TestA; Deny UPDATE ON Object::dbo.t to [Bobby]; Grant SELECT ON Object::dbo.t (a) to [Bobby]; Grant SELECT ON Object::dbo.t (d) to [Bobby]; Grant SELECT ON SCHEMA::dbo to [Bobby]; Grant ALTER ON FullText Catalog::[ftCat] to [Bobby] go use TestA; Grant REFERENCES ON Message Type::[//MyTest/Sample/ReplyMsg] to [Bobby] go use TestA; Grant ALTER ON Route::[ExpenseRoute] to [Bobby] Deny VIEW DEFINITION ON Route::[ExpenseRoute] to [Bobby] go use TestA; Grant ALTER ON Contract::[//Mytest/Sample/MyContract] to [Bobby] go use TestA; Deny ALTER ON Service::[//MyTest/Sample/InitSvc] to [Bobby] Grant VIEW DEFINITION ON Service::[//MyTest/Sample/InitSvc] to [Bobby] go use TestA; Grant ALTER ON FullText Catalog::[ftCat] to [Bobby] go use TestA; Grant ALTER ON FullText Stoplist::[mystopList] to [Bobby] Deny VIEW DEFINITION ON FullText Stoplist::[mystopList] to [Bobby] go use TestB; Grant CONNECT to [Bobby]; go use TestB; Deny VIEW DEFINITION ON Object::dbo.SimpleProc to [Bobby]; Grant EXECUTE ON Object::dbo.SimpleProc to [Bobby]; go use TestB; Grant VIEW DEFINITION ON Certificate::TestCert to [Bobby] go use TestB; Grant CONTROL ON ASYMMETRIC KEY::ASymKey to [Bobby]; go use TestB; Grant CONTROL ON SYMMETRIC KEY::SymKey1 to [Bobby]; Grant CONTROL ON SYMMETRIC KEY::SymKey2 to [Bobby]; go use TestB; Grant ALTER ON XML SCHEMA COLLECTION::dbo.XSC to [Bobby] Deny TAKE OWNERSHIP ON XML SCHEMA COLLECTION::dbo.XSC to [Bobby] go :connect TP_W520\sql2014 use master; create login Bobby with password=''; exec sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'securityadmin'; exec sp_addsrvrolemember @loginame = 'Bobby', @rolename = 'dbcreator'; Grant IMPERSONATE on Login::[sa] to [Bobby]; Grant VIEW DEFINITION on Login::[sa] to [Bobby]; Grant ALTER on Endpoint::[TSQL Default TCP] to [Bobby]; GRANT ALTER ANY LOGIN to [Bobby] WITH GRANT OPTION; Grant ALTER ANY SERVER ROLE to [Bobby]; Grant CONTROL SERVER to [Bobby]; Grant CONNECT SQL to [Bobby]; Grant VIEW SERVER STATE to [Bobby]; go use TestA; exec sp_addrolemember @rolename='TestRoleInTestA', @memberName='Bobby'; go use TestA; exec sp_addrolemember @rolename='db_securityadmin', @memberName='Bobby'; go use TestA; Grant CONNECT to [Bobby]; GRANT CREATE PROCEDURE to [Bobby] WITH GRANT OPTION; Grant CREATE TABLE to [Bobby]; go use TestA; Deny UPDATE ON Object::dbo.t to [Bobby]; Grant SELECT ON Object::dbo.t (a) to [Bobby]; Grant SELECT ON Object::dbo.t (d) to [Bobby]; Grant SELECT ON SCHEMA::dbo to [Bobby]; Grant ALTER ON FullText Catalog::[ftCat] to [Bobby] go use TestA; Grant REFERENCES ON Message Type::[//MyTest/Sample/ReplyMsg] to [Bobby] go use TestA; Grant ALTER ON Route::[ExpenseRoute] to [Bobby] Deny VIEW DEFINITION ON Route::[ExpenseRoute] to [Bobby] go use TestA; Grant ALTER ON Contract::[//Mytest/Sample/MyContract] to [Bobby] go use TestA; Deny ALTER ON Service::[//MyTest/Sample/InitSvc] to [Bobby] Grant VIEW DEFINITION ON Service::[//MyTest/Sample/InitSvc] to [Bobby] go use TestA; Grant ALTER ON FullText Catalog::[ftCat] to [Bobby] go use TestA; Grant ALTER ON FullText Stoplist::[mystopList] to [Bobby] Deny VIEW DEFINITION ON FullText Stoplist::[mystopList] to [Bobby] go use TestB; Grant CONNECT to [Bobby]; go use TestB; Deny VIEW DEFINITION ON Object::dbo.SimpleProc to [Bobby]; Grant EXECUTE ON Object::dbo.SimpleProc to [Bobby]; go use TestB; Grant VIEW DEFINITION ON Certificate::TestCert to [Bobby] go use TestB; Grant CONTROL ON ASYMMETRIC KEY::ASymKey to [Bobby]; go use TestB; Grant CONTROL ON SYMMETRIC KEY::SymKey1 to [Bobby]; Grant CONTROL ON SYMMETRIC KEY::SymKey2 to [Bobby]; go use TestB; Grant ALTER ON XML SCHEMA COLLECTION::dbo.XSC to [Bobby] Deny TAKE OWNERSHIP ON XML SCHEMA COLLECTION::dbo.XSC to [Bobby] go
You will see the generated script is a little bit different from our summary script in that there are new GRANT CONNECT statements (highlighted for easy identification), but this actually the default behavior when you create an account, by default the CONNECT permission is granted, which makes perfect sense. Otherwise, why bother creating an account in the first place if CONNECT is not allowed?
Now let's try to clone [Bobby] to a new account [Johnny] and after that, generate a permission auditing script for [Johnny]. We can use the following two lines.
# clone [Bobby] to [Johnny] Clone-SQLLogin -Server $Env:ComputerName, "$ENV:COMPUTERNAME\sql2014" -OldLogin Bobby -NewLogin Johnny -NewPassword "P@s$w0Rd" -Execute; # generate a permission auditing script, change parameter valeus to your needs, make sure [OldLogin] and [NewLogin] are same. Clone-SQLLogin -Server $Env:ComputerName, "$ENV:COMPUTERNAME\sql2014" -OldLogin Johnny -NewLogin Johnny -FilePath "c:\temp\Johnny_perm.sql";
We can compare the previous c:\temp\Bobby_perm.sql with this new c:\temp\Johnny_perm.sql and see they are exactly the same except that "Bobby" is replaced by "Johnny".
Summary
Finding / cloning the permissions of a user account inside SQL Server is a common task for DBAs and in this tip we created an advanced PowerShell function to do this work against multiple servers, there is no need to install anything on the target servers.
It is recommended that this PS script is put into a module you normally use, so it will be auto loaded when you load the module in your PS profile.
This script works for my current requirements, but I know there are still two things missing, i.e. search property list and Availability Group, which are available for SQL Server 2012 and later. If I add them into this PS function, I need to add a condition check for the SQL Server version, but since I do not have such needs in my current environment, I just omitted them.
Next Steps
- To understand the T-SQL solution to this problem, I highly recommend Brian Kelley's articles listed below:
- Cloning User Rights
- Re-generating SQL Server Logins by Claire Hsu, she has Part2 and Part 3 for the same topic
- There is a similar PowerShell login migration function online, called Copy-SQLLogin , I'll leave it to readers to compare it with the script in this tip. (hint: compare database level object permissions)
About the author
Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.
View all my tips