利用PowerShell複製SQLServer賬戶的所有許可權

来源:http://www.cnblogs.com/wenBlog/archive/2016/12/21/6206572.html
-Advertisement-
Play Games

問題 對於DBA或者其他運維人員來說授權一個賬戶的相同許可權給另一個賬戶是一個很普通的任務。但是隨著伺服器、資料庫、應用、使用人員地增加就變得很枯燥乏味又耗時費力的工作。那麼有什麼容易的辦法來實現這個任務嗎？ 當然，作為非DBA在測試甚至開發環境也會遇到這種問題，要求授予所有伺服器資料庫的某個許可權給一 ...


問題

　　對於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. 使用 Login.EnumDatabaseMappings()來查找每個存在資料庫登陸賬戶映射$OldLogin賬戶關係的資料庫
  4. 在每個映射用戶的資料庫中，我們可以通過 Database.EnumDatabasePermissions , Database.EnumObjectPermissions, User.EnumRoles, 和 EnumObjectPermissions 來獲得用戶的證書、對稱以及非對稱秘鑰、ServiceBrokers等等來檢索用戶的所有許可權。
  5. 所有檢索到的許可權信息將被添加到一個哈希表的數組彙總，然後通過迴圈數組導出許可權腳本到一個文件中或者運行這個腳本用來複制一個新的賬戶許可權。

測試環境

　　現在我把從網上找到的腳本進行修改完善，然後如下的腳本列出來如下：

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

 

 

在這個環境中，把所有不同的grant/deny 許可權，來自用戶[Bobby]的許可權，不論是伺服器登陸賬戶還是資料庫賬戶的許可權都獲取了。總之，這就是一個許可權 的grant/deny 腳本。

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

 

 

　　在我本地的電腦上，我有兩個資料庫實例，一個叫做[TP_W520](預設)，另一個叫做[TP_W520\SQL2014]。分別在兩個實例上運行。ok，接下來就是PowerShell 腳本了。

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

 

開始測試

　　打開一個PowerShell ISE的視窗，複製、黏貼這個PS腳本到一個新的視窗，然後還需要取消最後一行的註釋(還有修改伺服器參數的名稱：-Server parameter)，接著運行腳本。

你將會看到一個新生成位於c:\temp\Bobby_perm.sql 的腳本。然後在NotePad 中打開這個腳本，如下：




: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





 





　註意：　看到生成的腳本與我們之前總結的有一點不同，因為授權的同時預設授權的了連接許可權。否則，如果連接不被許可那麼第一步創建賬戶都不能實現。


現在我們看一下複製[Bobby]許可權到新賬戶[Johnny]。其中為[Johnny]生成許可權審計腳本。使用如下兩行：




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





我們可以比較之前的c:\temp\Bobby_perm.sql與新的c:\temp\Johnny_perm.sql  然後發現他們是完全一樣的除了賬戶名稱。


總結


　　查找並複製用戶的許可權在SQLServer內是一個普遍的任務。利用這個技巧我們可以創建一個高級的PowerShell 函數來做這個工作來處理多伺服器的情況，沒必要去分別到目標伺服器去執行代碼。同時建議將這個PS腳本放到一個module中來正常使用，因此當你需要的時候只需要加在PS文件就可以自動載入該功能了。


　　這個腳本適合我當前的工作，但是如果想進一步升級這個功能比如屬性列表和可利用群組等許可權則還需要進一步完善，同時要求資料庫是2012及其以後版本才能支持。由於目前我的伺服器還存在大量2008r2 所有我只能暫時忽略這些了。不過目前看也是夠用了。


 



              
	   




	

	
您的分享是我們最大的動力!




			 	

	
	

    


-Advertisement-
Play Games
     


	


    

    

    




	       	  

              
更多相關文章  

              

                   
    


				
  • 
				
    
			    	
    
				    	Android之文件數據存儲
			    	
    
					
    一、文件保存數據介紹 Activity提供了openFileOutput()方法可以用於把數據輸出到文件中，具體的實現過程與在J2SE環境中保存數據到文件中是一樣的。文件可用來存放大量數據，如文本、圖書、音頻等。 File對象適合按照從開始到結束的順序不跳過地讀取或寫入大量數據。例如，它適合於圖片文 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	AFNetworking報錯"_UTTypeCopyPreferredTagWithClass", referenced from: _AFContentTypeForPathExtens
			    	
    
					
    問題： 在和Unity交互的過程中，從Unity開發工具打包出來的項目文件，在添加AFNetworking庫，運行時報出以下錯誤： Undefined symbols for architecture armv7: "_kUTTagClassMIMEType", referenced from: - ...
    
				
    
		    
  • 
				
    
			    	
    
				    	iOS10之Expected App Behaviors
			    	
    
					
    昨天上架到appStore的時候碰到個問題，構建好後上傳到itunesconnect的的包都用不了， 顯示錯誤為：此構建版本無效。 或者英文顯示為：ITC.apps.preReleaseBuild.errors.invalidBinary 由於和itunesconnect帳號綁定的郵箱暫時進不去，沒 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	Android Weekly Notes Issue #236
			    	
    
					
    本期內容包括: Google的物聯網平臺Android Things; FileProvider; Android Studio的Layout Preview使用; Retrofit2使用; Google Sign-In和SmartLock; 把敏感信息放入NDK的解決方式.  設計部分討論了調色板... ...
    
				
    
		    
  • 
				
    
			    	
    
				    	違章查詢源碼分享
			    	
    
					
    使用快遞100查詢介面實現 源碼地址：https://github.com/chenjie200280/weizhang 測試安裝包：http://files.cnblogs.com/files/ttsofts/weizhang.apk 1.查詢界面 2.查詢結果1 （有違章的信息） 3.查詢結果3 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	React Native Android gradle下載慢問題解決
			    	
    
					
    很多人會遇到 初次運行 react native run android的時候 gradle下載極慢，甚至會失敗的問題 如下圖 實際上這個問題好解決的 1. 首先 把對應版本的 下載到本地任意一個磁碟里 比如說我在到了 2. 然後拖拽 文件夾到 瀏覽器 就會得到 的訪問地址 後面加上文件名 得到 的 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	SQL Server事務、視圖和索引
			    	
    
					
    廢話不多說，直接上乾貨 14:13:23 事務 概括：事務是一種機制，一個操作序列，包含一組資料庫操作命令，並且把所有的命令作為一個整體一起 向系統提交或撤銷操作 請求。 事務的特性： 1.原子性：事務是一個完整的操作，事務的各元素都是不可分的（原子的）。事務中的一組命令要麼都執行，要麼都不執 行。 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	資料庫 DML、DDL、DCL區別 .
			    	
    
					
    總體解釋： DML（data manipulation language）： 它們是SELECT、UPDATE、INSERT、DELETE，就象它的名字一樣，這4條命令是用來對資料庫里的數據進行操作的語言 DDL（data definition language）： DDL比DML要多，主要的命令有 ...
    
				
    
		    
    • 
				 
		  
  
                  

              

          



		
        

        
        

            
 







    
一周排行

    

         
    

 
    -Advertisement-
    Play Games
    	   
	
  • 
			        	
    
			    	        
    
				    	        移動開發（一）：使用.NET MAUI開發第一個安卓APP
			            	
    
				        	
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf ToggleButton選中效果和一個登錄界面
			            	
    
				        	
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        彈幕樹洞項目功能新增篇
			            	
    
				        	
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第27篇 sqlserver2022詳細安裝步驟
			            	
    
				        	
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 開源高性能 MQTT 類庫
			            	
    
				        	
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
			            	
    
				        	
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
			            	
    
				        	
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
			            	
    
				        	
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
			            	
    
				        	
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第28篇 如何.net中實現高效可靠數據同步api
			            	
    
				        	
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類