测量试验景况

  今后本人把从网络找到的本子进行改变完善,然后如下的脚本列出来如下:

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

 

SET @ReversedPath = REVERSE(@Path)
SELECT @FileName = RIGHT(@Path, CHARINDEX(‘/’, @ReversedPath)-1)

0.2-给root顾客设置密码:mysql>update
user set password=password(“新密码”) where user=”root”;
                         
                         例:update user set
password=password(“11111111″) where user=”root”;
0.3-刷新数据库(必须要记得):mysql>flush
privileges;
0.4-退出mysql:mysql> quit
===1-MySQL服务器相关命令===
1.1-连接MySQL服务器:\>mysql
-uroot -h127.0.0.1 -ppassword
                         
      \>mysql -u客商名 -hMySQL服务器地址 -p客商密码

总结

  查找并复制用户的权柄在SQLServer内是八个广阔的职责。利用这些技能大家可以成立一个高档的PowerShell
函数来做那个专门的学业来拍卖多服务器的动静,没须求去分别到指标服务器去实践代码。同有时间提议将这么些PS脚本放到二个module中来常常使用,由此当您须要的时候只须要加在PS文件就足以自动加载该意义了。

  那几个本子切合本人当下的劳作,然则假诺想进一步升高这一个职能比方属性列表和可接纳群组等权力则还必要更上一层楼周全,同期必要数据库是二〇一二及其将来版本技艺援助。由于近期本身的服务器还存在大气2010r2
兼有小编不得不不时忽视这一个了。不过当下看也是十足了。

 

SET @ReversedPath = REVERSE(@Path)
SET @FileName = ”
SELECT @ExtLength = CHARINDEX(‘.’, @ReversedPath)
IF (@ExtLength > 0)
BEGIN
SELECT @FileName = RIGHT(@Path, @ExtLength – 1)
END
RETURN @FileName
END
GO
/****** Object: UserDefinedFunction [dbo].[GetFileName] Script
Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetFileName]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)

4.2-删除记录:delete from 数据表名
where 条件
      例:delete from
tb_people where user=’wha’;
//通常条件是钦点id删除的,未有where条件则删除全体表
4.3-查询数据库记录:
    1)select * from
数据表; //查询钦赐表内全体数据
     例:select * from
tb_people;
    2)select * from
数据库名.数据表名; //查询钦赐数据库的钦命表内的数额
     例:select * from
db_czy.tb_people;
    3)select
部分字段名 from 数据表 where 查询的相干规范;
//查询钦定表内钦定字段和某字段值的多少
     例:select
id,user,password,email from tb_people where id=1;
4.4-修改记录:update 数据表名 set
column_name1=new_value1,column_name2=new_value2,..[where
condition];
        update
数据表名 set 字段名1=新的值1,字段名2=新的值2,.. [where 条件];
      例:update
tb_people set password=’12345678′,email=’15707950645@163.com’ where
user=’czy’;
===5-MySQL数据备份和复苏===
5.1-数据备份:C:\Users\Administrator>mysqldump
-u客商名 -p客户密码 数据库名 >备份文件积累路线和文书名

缓慢解决方案

  要是今年我们互连网去搜寻施工方案,大大多时候搜到的都以行使T-SQL技术方案,但是这又会发生上边多少个小难点:

  1. 大家要求到对象服务器上执行这几个本子,有的以至还索要配备后实施叁遍。
  2. 不能够生成那些T-SQL脚本到三个文书中。
  3. 重度使用的动态脚本代码冗长不便利阅读和维护。

本篇本事的要害目标正是提供八个越来越好的依附PowerShell和SMO的化解方案来消除上述难题。

END

                  例:mysql -uroot -p11111111 db_czy
<D:\File\phpEnv\MySQL\data_backup.txt

发端测量检验

  展开二个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 
然后意识他们是全然一样的除了账户名称。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetDirectoryPath]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @PathLength INT

           例:\>mysql -uroot -hlocalhost
-p11111111
1.2-断开MySQL服务器:mysql>quit;
1.3-停止MySQL服务器:
    1)\>net stop
mysql
  
  或2)\>mysqladmin -uroot shutdown -ppassword
   
 例:\>mysqladmin -uroot shutdown -p11111111
1.4-启动MySQL服务器:\>net start
mysql
===2-MySQL数据库操作===
2.1-创制数据库:create database
数据库名;
       例:create
database db_czy;
2.2-查看数据库:show
databases;
2.3-选取数据库:use db_czy;
2.4-删除数据库:drop database
数据库名;
       例:drop
database db_czy;
===3-MySQL数据表操作===
3.1-在当前增选的数据库中创设数据表:
    create table
数据表名(字段名1 属性,字段名2 属性,…);
    例:create table
tb_people(
      id int
auto_increment primary key,
      user
varchar(30) not null,
      password
varchar(30) not null,
      email
varchar(50) not null,
      createtime
datetime
    );
3.2-查看当前挑选的数据库中的数据表:show
tables;
3.3-查看表结构:
    1.1)show columns
from 数据表名 from 数据库名;
     例:show columns
from tb_people from db_czy;
     或1.2)show columns
from 数据库名.数据表名;
     例:show columns
from db_czy.tb_people;
   或2.1)describe
数据表名;
     例:describe
tb_people;
  或2.1.1)desc 数据表名
列名;
     例:describe
tb_people password; //能够只列出某一列音信
3.4-修改表结构:alter table 数据表名
相关操作;
          例:alter
table tb_people add email varchar(50) not null, modify user
varchar(40);
                     
//增添二个新字段email,类型为varchar(50),not
null,将字段user的连串改为varchar(40).
3.5-重命名表:rename table 数据表名1
to 数据表名2;
      例:rename
table tb_people to tb_czy;
        rename
table tb_czy to tb_people;
3.6-删除表:drop table
数据表名;
     例:drop table
tb_people;
===4-MySQL数据增加和删除查改操作===
4.1-插入记录:insert into
数据表名(字段名1,字段名2,..)values(值1,值2,..);  //一遍可插入多行记录,标准SQL语句只好三次插一行
      例:insert into
tb_people(user,password,email,createtime)
      
 values(‘czy’,’11111111′,’2384439266@qq.com’,’2016-10-10
02:36:50′);
        insert into
tb_people(user,password,email,createtime)
      
 values(‘cml’,’22222222′,’22222222@qq.com’,’2016-10-10 02:40:36′);
 //values(‘wha’,’33333333′,’33333333@qq.com’,’2016-10-10
02:45:25′);

问题

  对于DBA只怕其余运转职员的话授权二个账户的均等权限给另二个账户是叁个很普通的职分。可是随着服务器、数据库、应用、使用人口地扩展就变得很枯燥无味又耗费时间费劲的行事。那么有怎样轻便的情势来兑现那个职分吗?

当然,作为非DBA在测量试验以致开采条件也会遇见这种题材,供赋予以全体服务器数据库的某些权限给一位的时候。我们是否有何样其余方法提升效用?

CREATE FUNCTION [dbo].[GetFileNameWithoutExtension]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @ExtLength INT

===0-MySQL密码设置===
0.1-登录和步向MySQL数据库:

发表评论

电子邮件地址不会被公开。 必填项已用*标注