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

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"&gt;  
    <xsd:complexType mixed="true"&gt;  
        <xsd:sequence>  
            <xsd:element name="Location" minOccurs="1" maxOccurs="unbounded"&gt;  
                <xsd:complexType mixed="true"&gt;  
                    <xsd:sequence>  
                        <xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />  
                    </xsd:sequence>  
                    <xsd:attribute name="LocationID" type="xsd:integer" use="required"/&gt;  
                    <xsd:attribute name="SetupHours" type="xsd:decimal" use="optional"/&gt;  
                    <xsd:attribute name="MachineHours" type="xsd:decimal" use="optional"/&gt;  
                    <xsd:attribute name="LaborHours" type="xsd:decimal" use="optional"/&gt;  
                    <xsd:attribute name="LotSize" type="xsd:decimal" use="optional"/&gt;  
                </xsd:complexType>  
            </xsd:element>  
        </xsd:sequence>  
    </xsd:complexType>  
</xsd:element>  

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