问题

　　对于DBA或者其他运维人员来说授权一个账户的相同权限给另一个账户是一个很普通的任务。但是随着服务器、数据库、应用、使用人员地增加就变得很枯燥乏味又耗时费力的工作。那么有什么容易的办法来实现这个任务吗？

当然，作为非DBA在测试甚至开发环境也会遇到这种问题，要求授予所有服务器数据库的某个权限给一个人的时候。我们是不是有什么其他办法提高效率？

解决方案

　　如果这个时候我们网上去搜索解决方案，大多数时候搜到的都是使用T-SQL解决方案，但是这又会产生下面几个小问题:

1. 我们需要到目标服务器上执行这些脚本，有的甚至还需要部署后执行一遍。
2. 不能生成这些T-SQL脚本到一个文件中。
3. 重度使用的动态脚本代码冗长不方便阅读和维护。

本篇技巧的主要目的就是提供一个更好的基于PowerShell和SMO的解决方案来解决上述问题。

新的PS方法

1. 在cmdlet函数中，可以接收一个SQLServer实例名称的列表以及登陆名（$OldLogin）,这些登陆名的权限是准备复制的。
2. 对于每个实例，使用SMO Server.EnumObjectPermissions(loginName) 来获取服务对象（如登陆账号）权限并且使用Server.EnumServerPermissions(loginName) 来获取服务器级别的权限。
3. 使用来查找每个存在数据库登陆账户映射$OldLogin账户关系的数据库
4. 在每个映射用户的数据库中，我们可以通过,, 和 EnumObjectPermissions 来获得用户的证书、对称以及非对称秘钥、ServiceBrokers等等来检索用户的所有权限。
5. 所有检索到的权限信息将被添加到一个哈希表的数组汇总，然后通过循环数组导出权限脚本到一个文件中或者运行这个脚本用来复制一个新的账户权限。

测试环境

　　现在我把从网上找到的脚本进行修改完善，然后如下的脚本列出来如下：

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"&gt;  
    <xsd:complexType mixed="true"&gt;  
        <xsd:sequence>  
            <xsd:element name="Location" minOccurs="1" maxOccurs="unbounded"&gt;  
                <xsd:complexType mixed="true"&gt;  
                    <xsd:sequence>  
                        <xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />  
                    </xsd:sequence>  
                    <xsd:attribute name="LocationID" type="xsd:integer" use="required"/&gt;  
                    <xsd:attribute name="SetupHours" type="xsd:decimal" use="optional"/&gt;  
                    <xsd:attribute name="MachineHours" type="xsd:decimal" use="optional"/&gt;  
                    <xsd:attribute name="LaborHours" type="xsd:decimal" use="optional"/&gt;  
                    <xsd:attribute name="LotSize" type="xsd:decimal" use="optional"/&gt;  
                </xsd:complexType>  
            </xsd:element>  
        </xsd:sequence>  
    </xsd:complexType>  
</xsd:element>  

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];

?

在这个环境中，把所有不同的grant/deny 权限，来自用户[Bobby]的权限，不论是服务器登陆账户还是数据库账户的权限都获取了。总之，这就是一个权限 的grant/deny 脚本。

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 = '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

　　在我本地的电脑上，我有两个数据库实例，一个叫做[TP_W520](默认)，另一个叫做[TP_W520SQL2014]。分别在两个实例上运行。ok，接下来就是PowerShell 脚本了。

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 domainusername,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:ComputerNamesql2014" -OldLogin Bobby -NewLogin Bobby -FilePath "c:tempBobby_perm.sql";

开始测试

　　打开一个PowerShell ISE的窗口，复制、黏贴这个PS脚本到一个新的窗口，然后还需要取消最后一行的注释(还有修改服务器参数的名称：-Server parameter)，接着运行脚本。

你将会看到一个新生成位于c:tempBobby_perm.sql 的脚本。然后在NotePad 中打开这个脚本，如下：