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

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

#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";