利用PowerShell复制SQLServer账户的所有权限

-- summary script
-- as server Login account
use Master;
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 VIEW SERVER STATE TO [bobby];
-- 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]
-- 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

#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"
                $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"};}
            { 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"}
                                                                { $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"}
                                                                { $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
                        [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
           [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";