我发现学习PowerShell,作为非DBA在测试甚至开发环

作者: www.9159.com  发布:2019-11-29

问题

  对于DBA或者其他运维人员来说授权一个账户的相同权限给另一个账户是一个很普通的任务。但是随着服务器、数据库、应用、使用人员地增加就变得很枯燥乏味又耗时费力的工作。那么有什么容易的办法来实现这个任务吗?

当然,作为非DBA在测试甚至开发环境也会遇到这种问题,要求授予所有服务器数据库的某个权限给一个人的时候。我们是不是有什么其他办法提高效率?

♦  学会了 Windows Management Instrumentation (WMI)相关的知识,它能允许我只使用一条信息就查询多一台或者多台服务器。

请记住,如果在服务器上您有循环的错误日志,您需要查找的行可能不在当前的错误日志里。您将需要调整以下的命令,通过在ERRROLOG后追加1,2,3等等,来查找错误日志存档。

测试环境

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

-- 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_W520SQL2014]。分别在两个实例上运行。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 domainusername, 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:ComputerNamesql2014" -OldLogin Bobby -NewLogin Bobby -FilePath "c:tempBobby_perm.sql";

 

从SysAdmin角色中移除BULTINAdministrator组

引用:

总结

  查找并复制用户的权限在SQLServer内是一个普遍的任务。利用这个技巧我们可以创建一个高级的PowerShell 函数来做这个工作来处理多服务器的情况,没必要去分别到目标服务器去执行代码。同时建议将这个PS脚本放到一个module中来正常使用,因此当你需要的时候只需要加在PS文件就可以自动加载该功能了。

  这个脚本适合我当前的工作,但是如果想进一步升级这个功能比如属性列表和可利用群组等权限则还需要进一步完善,同时要求数据库是2012及其以后版本才能支持。由于目前我的服务器还存在大量2008r2 所有我只能暂时忽略这些了。不过目前看也是够用了。

 

♦  学会了如何使用 Server Management Objects (SMO) 来实现数据库相关任务的自动化执行。

# create ListAdmins function to list local Administrators on a server.

# usage: ListAdmins ServerName

function ListAdmins ($svr)

{

$domain = [ADSI]""

$strComputer = $svr

$computer = [ADSI]("WinNT://" + $strComputer + ",computer")

$computer.name;

$Group = $computer.psbase.children.find("administrators")

$Group.name

$members= $Group.psbase.invoke("Members") | %{$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}

$members

} 

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

这个查询按服务器排序,返回非微软(Non-Microsoft supplied,我猜想作者是指SqlServer 系统数据库)提供的数据库名称。之后,我会将之与一份数据库目录系统产生的报表进行对比。

# checking three dump locations on a default instance.

write-host ''

write-host 'Server1'

get-childitem \Server1e$dump_dataServerName*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

get-childitem \Server1g$dump_dataServerName*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

get-childitem \Server1i$dump_data ServerName *.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

# checking one dump location on a named instance.

write-host ''

write-host ' Server2'

get-childitem \Server2ShareNamedump_dataServerNameInstancedb_dump*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

开始测试

  打开一个PowerShell ISE的窗口,复制、黏贴这个PS脚本到一个新的窗口,然后还需要取消最后一行的注释(还有修改服务器参数的名称:-Server parameter),接着运行脚本。

你将会看到一个新生成位于c:tempBobby_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_W520sql2014 
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:COMPUTERNAMEsql2014" -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:COMPUTERNAMEsql2014" -OldLogin Johnny -NewLogin Johnny -FilePath "c:tempJohnny_perm.sql";

我们可以比较之前的c:tempBobby_perm.sql与新的c:tempJohnny_perm.sql  然后发现他们是完全一样的除了账户名称。

 SQL 任务

SMO

解决方案

  如果这个时候我们网上去搜索解决方案,大多数时候搜到的都是使用T-SQL解决方案,但是这又会产生下面几个小问题:

  1. 我们需要到目标服务器上执行这些脚本,有的甚至还需要部署后执行一遍。
  2. 不能生成这些T-SQL脚本到一个文件中。
  3. 重度使用的动态脚本代码冗长不方便阅读和维护。

本篇技巧的主要目的就是提供一个更好的基于PowerShell和SMO的解决方案来解决上述问题。

# List aliases, sort by name or definition

get-alias | sort name

get-alias | sort definition

 

     检查多台服务器上的SQLServer版本

下面这些纯PowerShell的例子,回答了一些DBA也许会有的疑问。

# Remove BUILTINAdministrators from sysadmin role

function rmba ($s)

{

   $svr="$s"

   $cn = new-object System.Data.SqlClient.SqlConnection"server=$svr;database=master;Integrated Security=sspi"

   $cn.Open()

   $sql = $cn.CreateCommand()

   $svr

   $sql.CommandText = "EXEC master..sp_dropsrvrolemember @loginame = N'BUILTINAdministrators', @rolename = N'sysadmin';"

   $rdr = $sql.ExecuteNonQuery();

}

为什么这个SQL Server DBA学习PowerShell

自动化重复任务

# Find a login or AD group on multiple servers

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

foreach ($svr in get-content "C:AllServers.txt")

{

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

trap {"Oops! $_"; continue } $srv.Logins | where {$_.Name -eq 'DOMAINITS_DATA_ADMIN'} | select Parent, Name

}

      我将演示的例子中,我使用一个Foreach循环对这个清单里列出的每台服务器执行一个任务。这个简单的服务器列表构成了完成重复任务的基石。我主要的工作是在Microsoft的环境里,我发现使用PowerShell执行重复任务要比之前用Python快。例如,Python需要多行语句读取,打开和关闭一个文件,但是PowerShell中Get-Content cmdlet读取一个文件只使用一行代码。

Joe.TJ翻译整理,仅用于传播资讯之目的。

rmba ServerName

下面的脚本把列出服务器上SysAdmin成员所需的PowerShell代码封装成了一个函数。

 

 

      PowerShell 使得为我所有的服务器实现自动化常规的和重复性任务变得更容易,使得用一些关于服务器的位信息(bit of information)便能快速和高效处理看似层出不穷的即席请求。接下来的章节只是描述一些我已经写好的,用来实现自动化重复性任务的脚本。这些例子的进步来自:我发现那些曾经花了很多精力解决才解决的问题,转换成Powershell来处理则变得非常简单。

每天早上我执行如下脚本来检查在我的服务器上任何失败的SQL代理作业:

         Server3

  • Books24x7.com – subscription required"> Microsoft ADO.NET 2.0 Step by Step by Rebecca M. Riordan

每个月我都要核对实际的数据库目录和一个被其它应用程序作为资源引用的内部开发的数据库目录系统。

列出服务器上的本地管理员

gc "C:AllServers.txt"

 

♦  更加适应OOP.

SMO是一个对象集合,它允许你自动化任何Microsoft SQL  Server相关的管理任务。同样的,对于不熟悉面向对象编程的DBA来说,最大的障碍就是使用更令人生畏的对象模型。同样的,像WMI一样,您需要知道如何检查一个对象以确定它可用的属性和方法。

     下面的这个脚本遵循我用于对多台服务器执行SQL脚本的标准模板。它用foreach循环读取服务器清单,连接到服务器和执行一个返回用户数据库名称的SQL查询。为了这篇文章,我已经编辑过例子的格式,注释用绿色,PowerShell代码用蓝色,SQL用红色。

  # List all installed hotfixes on a server

get-wmiobject Win32_QuickFixEngineering

# Check if a specific hotfix is installed on a server

get-wmiobject Win32_QuickFixEngineering | findstr KB928388

# create sa function to list sysadmin members

# usage: sa ServerName

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

function sa ($s)

{

$svr="$s"

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$svrole = $srv.Roles | where {$_.Name -eq 'sysadmin'}

$svr

$svrole.EnumServerRoleMembers()

}

      把Python转换成PowerShell最简单的任务就是对多台服务器执行一条语句。在这些例子中基本的步骤如下:

依据你前面的经验,搞明白SMO对象模型是如何运作的可能很棘手。我明白基础的面向对象的编程知识,但不是完全理解,直到我执行一个列出服务器上SysAdmin角色成员的脚本时。起初,我尝试使用以下脚本并收到如图所示错误信息。(我在测试时,没有遇到作者所说的错误信息,只是把$svrole当作一个字符串变量输出,没有得预期角色成员的结果。所以这里就直接引用了作者的图。)

图片 1

查找端口号

PowerShell即是交互式命令行也是脚本环境。我刚开始着手解决一个问题是通过在命令行中执行命令。当我已经确定了命令的正确顺序时,我把它们保存为一个以.ps1扩展名的脚本文件,当需要时再执行。

Joe.TJ翻译整理,仅用于传播资讯之目的。

短短几行PowerhShell代码,怎么能做这么多的事情,这实在很惊人。

使用PowerShell管理多台服务器

为了研究不同的对象,相应地改变上述脚本中的第二行和第三行。

♦  提高了我关于.NET的认识,这样我就能够更好地与我所支持的应用程序开发人员交流。

下面的例子以5到7行代码就结束了(这也取决于你的格式编排),但是不管你的服务器清单上有多少服务器,这将会找出登录/组。

         在我的例子中,我使用一个简单的包含我的服务器的清单AllServers.txt。格式如下:

浏览SMO类

         ……

列出服务器上SysAdmin角色的成员

# Read a file

get-content "C:AllServers.txt"

我经常被开发者问到命名实例的端口号。通过一个短短的命名管道结合两个cmdlet:Get-Content和Select-String。你就可以通过一行程序在错误日志中找到端口号。这比手动查找错误日志或者执行一段SQL代码快多了。

 

# generate a random password

[Reflection.Assembly]::LoadWithPartialName(”System.Web” ) | out-null

[System.Web.Security.Membership]::GeneratePassword(10,2) # 10 bytes long

[System.Web.Security.Membership]::GeneratePassword(8,2) # 8 bytes long

   如果你感觉要打过多的字,你可以通过它的别名来调用Get-Content cmdlet。

# Check for failed SQL jobs on multiple servers

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

foreach ($svr in get-content "C:AllServers.txt")

{

write-host $svr

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize

} 

为什么这个SQL Server DBA学习PowerShell

图片 2

 

如果您需要为SQL登录生成随机密码,您可以使用如下所示的.NET类:

为了便于阅读而定义的最佳实践是在命令行中使用别名和在脚本中完成cmdlet。您可以使用Get-Alias cmdlet列出所有PowerShell中的别名:

# To examine the SMO Server object in PowerShell:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "ServerName"

$svr | get-member

此函数接受一个参数,与服务器建立连接,然后执行sp_dropsrvrolememeber系统存储过程。

  • SQL Server Books Online:
  • Blogs:

核对实际的数据库目录和内部数据库目录

WMI

这个脚本定义了一个函数而不是foreach循环,允许我在任何服务器上从SysAdmin角色中移除BULTINAdministrator组。仅键入:

# Before I understood the concept of objects completely, I tried…

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$svr="ServerName"

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$svrole = 'sysadmin'

$svrole 

如蒙转载或引用,请保留以下内容:
Joe's Blog:

 

      在这篇文章里,我描述了一些使用PowerShell的例子,同时希望这些对DBA有用。我的脚本将会演示在一台或多服务器如何执行SQL查询,WMI查询和SMO代码,以及帮助您更好的管理多台数据库服务器。所有脚本均在SQL Server 2005中测试通过。

我使用下面的脚本(及前一个)保持在服务器和SQL Server上拥有Admin权限的人数最少。这个例子由Microsoft MVP Ying Li所写并贴在他的博客上。它演示了如何列出服务器上的本地管理员。这个函数接收一个服务器名称,然后连接指定的服务器并列出其上的本地管理员组的成员。

 

 

          我运行如下脚本来确定,所有服务器是否处于公司规定的补丁级别:

 

         Server2

get-content \ServerNameShareNameMSSQL2000MSSQL`$SQL100LOGERRORLOG | select-string "listening"

这个脚本节省我的时间,因为我不必要跳转到SSMS去完成这个任务。在SMO章节中您会发现两个其它我创建的函数,它们用来列出BULTINAdministrator和服务器本地管理员的成员。

ADO.Net

# SQLVer.ps1

# usage: ./SQLVer.ps1  

# Check SQL version

foreach ($svr in get-content "C:dataAllServers.txt")

{

  $con = "server=$svr;database=master;Integrated Security=sspi"

  $cmd = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) | out-null

  $svr

  $dt | Format-Table -autosize

}        

如果服务器上有多个驱动器需要检查,我就为额外的驱动器重复执行Get-ChildItem cmdlet。这里是我ChkBkups.ps1脚本的一个片段:

 

脚本中Trap语句处理连接服务器时发生的错误。在这个例子中,如果连接服务器时有错误,将会返回服务器名称和错误信息。偶尔,在输出中我会看到:“Oops!Failed to connect to the server ServerName”.

      本文的目的不是写成一份PowerShell教程。我假设您已经熟知以下内容:基本的PowerShell语法,如何使用cmdlets获取帮助,命令行是如何工作的,如何运行脚本,命名管道是什么,别名是什么等等。如果您不知道这些内容,你可以在各种线上文章,新闻组和博客中找到大量的帮助(文章结尾引用章节中列出了部分资源)。这篇文章中的部分脚本来源于我阅读这些资源时遇到的。

在我的环境中,我有两个数据库配置并且备份并不总放置在一个标准位置。因此,我用“Brute Force”方案来检查备份。

  1. 为每台服务器读取数据库服务器清单
  2. 创建一个表用于存储结果
  3. 建立与服务器的连接
  4. 执行查询并格式化查询结果
# Find a port number

gc \ServerNameShareNameMSSQL2005MSSQL.2MSSQLLOGERRORLOG | select-string "listening"

         Server1

检查多台服务器上失败的SQL 代理作业

     我开始学习PowerShell,是因为我在寻找一种快速和高效的方式收集有关我的SQL Servers的信息和更好地管理我的服务器工作负载的方式。我以为,我正在学习是另一门新的脚本语言,它能帮我做到前面提到的设想。实际上,我发现学习PowerShell,不仅提供了一种强大的手段去实现许多常规和重复性的服务器任务和健康检查;也是一个提高我其它方面技能的有用的跳板。例如,在我学习PowerShell时,我发现:

我认为使用PowerShell会让我成为一名更好的DBA,因为我有意识去自动化平常的任务,更快地收集有关服务器的信息,以及更好地管理我的服务器工作负载。我还发现,使用PowerShell会延伸我的知识到时那些我通常不会涉及的领域(这只会是一件好事情)。

# inv.ps1

# usage: ./inv.ps1

# Database inventory

foreach ($svr in get-content "C:dataAllServers.txt")

{

  $con = "server=$svr;database=master;Integrated Security=sspi"

  $cmd = "SELECT name FROM master..sysdatabases WHERE dbid > 4 AND name NOT IN ('tracedb','UMRdb','Northwind','pubs','PerfAnalysis') ORDER BY name"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) | out-null

  $svr

  $dt | Format-Table -autosize

} 

在SMO的例子中,您将会再次看被用于执行SMO代码的Foreach循环。所有的例子通过设定一个SMO程序集引用开始。一旦你建立了这个引用,那么脚本便能实例化从这个程序集类中派生的新对象。

         使用PowerShell管理多台服务器的核心就是一份简单的服务器清单,它包括您希望在其上执行常规任务和健康检查的服务器名字。

我最初的SMO例子之一,灵感来自于我的主管,她让我找出数据建模组能访问那些数据库服务器。她希望只是开发服务器能被这个组访问。

原文出自:http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/

总结:

原文出自:http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/

我曾尝试只用Select-String去搜索错误日志,但是由于某些原因,Get-String不能读取活动的错误日志,除非与Get-Content结合使用。下在的例子中我在错误日志中查找“Listening”一词。

在这一点上我有了小小的顿悟。最终使我理解了面向对象编程的概念和PowerShell中“任何东西都是一个对象”。我成功的创建了一个服务器对象的实例,并从那里,我想要用自己的方式为SysAdmin角色处理服务器角色对象。所以,我设定一个变量$svrole,并赋值‘sysadmin’。

如果你在SQL Server 2000的命名实例上搜索错误日志,你需要用反引号将文件路径中的$转义。如下所示:

查检多台服务器上的当前备份

在我看来,花时间学习PowerShell是用得其所。

虽然SMO类在联机丛书中有记录,但是如果你学会获取对象的属性和方法的列表也会很有用。为了浏览SMO类,你需要设定一个引用然后使用。Get-Member(gm) cmdlet会显示该对象的属性和方法。

我每天早上运行这个脚本。我们有一套每晚运行的自动化的例程,它们处理标准的DBA任务,像备份,完整性检查,索引维护等等。每个服务器维护进程会发邮件来报告它们的状态。这个脚本节省了我查看多封邮件的时间。

查询多台服务器上的登录或AD组

 

 检查已安装的修复程序

杂项任务

然后我尝试调用这个字符串对象的方法,并认为我是在调用服务器角色对象的方法。在这种情况下,变量$svrole只包含字符串对象而不是对服务器角色对象的引用。因此,才会发生上面的错误。

  • “Getting Started Guide”
  • Books.
  • “Windows PowerShell In Action” by Bruce Payette
  • Windows PowerShell: TFM” by Don Jones and Jeffery Hicks
  • Newsgroups
  • microsoft.public.windows.powershell
  • Blogs and articles:

PowerShell

生成随机密码

图片 3

# Checking backups are current

write-host ''

write-host 'ServerName'

get-childitem \ServerNameShareNamedump_data*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

本文由9159.com发布于www.9159.com,转载请注明出处:我发现学习PowerShell,作为非DBA在测试甚至开发环

关键词: