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

问题

　　对于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. 所有检索到的权限信息将被添加到一个哈希表的数组汇总，然后通过循环数组导出权限脚本到一个文件中或者运行这个脚本用来复制一个新的账户权限。

测试环境

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
-- 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 脚本。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 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 脚本了。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
#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]生成权限审计脚本。使用如下两行：






?
1
2
3
4
5
# 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 所有我只能暂时忽略这些了。不过目前看也是够用了。


 






            

            
posted @ 
 
DB乐之者 
阅读(2212) 
评论(0) 
编辑 
收藏 
举报


        

	    
	    
    









    
    

    
    



    

        
    

        

            
                
                
            
        

    

编辑推荐：



· Linux系列：如何用 C#调用 C方法造成内存泄露
    

· AI与.NET技术实操系列（二）：开始使用ML.NET
    

· 记一次.NET内存居高不下排查解决与启示
    

· 探究高空视频全景AR技术的实现原理
    

· 理解Rust引用及其生命周期标识（上）
    



    

    阅读排行：
    

 ·          阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
        

 ·          单线程的Redis速度为什么快？
        

 ·          SQL Server 2025 AI相关能力初探
        

 ·          展开说说关于C#中ORM框架的用法！
        

 ·          AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
        



    

    



	

	

	
	

	

	

	





    

    
    








  

  
点击右上角即可分享

  微信分享提示