对于 DBA 或者其他运维人员来说授权一个账户的相同权限给另一个账户是一个很普通的任务。但是随着服务器、数据库、应用、使用人员地增加就变得很枯燥乏味又耗时费力的工作。那么有什么容易的办法来实现这个任务吗?
当然,作为非 DBA 在测试甚至开发环境也会遇到这种问题,要求授予所有服务器数据库的某个权限给一个人的时候。我们是不是有什么其他办法提高效率?
如果这个时候我们网上去搜索解决方案,大多数时候搜到的都是使用 T-SQL 解决方案,但是这又会产生下面几个小问题:
本篇技巧的主要目的就是提供一个更好的基于 PowerShell 和 SMO 的解决方案来解决上述问题。
现在我把从网上找到的脚本进行修改完善,然后如下的脚本列出来如下:
- -- 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 所有我只能暂时忽略这些了。不过目前看也是够用了。
来源: