{"id":33465,"date":"2012-10-17T16:52:21","date_gmt":"2012-10-17T15:52:21","guid":{"rendered":"https:\/\/www.fuhaijun.com\/?p=33465"},"modified":"2024-11-06T11:18:46","modified_gmt":"2024-11-06T03:18:46","slug":"powershell%e5%b0%9d%e8%af%95%e7%99%bb%e5%bd%95sql-server","status":"publish","type":"post","link":"https:\/\/www.fuhaijun.com\/?p=33465","title":{"rendered":"PowerShell\u5c1d\u8bd5\u767b\u5f55SQL Server"},"content":{"rendered":"

      \u524d\u4e09\u7bc7\u6587\u7ae0\u4e2d\u521b\u5efa\u4e86PSNet\u7a0b\u5e8f\u96c6\uff0c\u5176\u4e2d\u5305\u542b\u4e86\u5bf9\u6307\u5b9aIP\u8fdb\u884c\u7aef\u53e3\u626b\u63cf\uff0c\u6536\u53d1TCP\u6d88\u606f\u5305\u548c\u6536\u53d1UDP\u6d88\u606f\u5305\u7684\u76f8\u5173\u529f\u80fd\uff0c\u4f5c\u4e3a\u8fd9\u662f\u6700\u57fa\u672c\u7684\u5bf9\u7f51\u7edc\u60c5\u51b5\u7684\u6700\u57fa\u672c\u68c0\u6d4b\uff0c\u540e\u7eed\u7684\u6587\u7ae0\u5c06\u4f1a\u5bf9\u6b64\u7a0b\u5e8f\u96c6\u8fdb\u884c\u4e0d\u65ad\u7684\u6269\u5145\u4f7f\u5176\u5305\u542b\u66f4\u5168\u9762\u7684\u529f\u80fd\u3002\u4f46\u662f\u5149\u6709\u8fd9\u4e9b\u7b80\u5355\u7f51\u7edc\u63a2\u6d4b\u7684\u529f\u80fd\u8fd8\u8fdc\u8fdc\u4e0d\u591f\uff0c\u4e3a\u4e86\u80fd\u66f4\u5168\u9762\u7684\u4f7f\u7528PowerShell\u9488\u5bf9\u7f51\u7edc\u5b89\u5168\u8fdb\u884c\u68c0\u6d4b\uff0c\u5728\u672c\u6587\u4e2d\u5c06\u4f1a\u521b\u5efaPSSecurity\u7a0b\u5e8f\u96c6\u7528\u4e8e\u5b58\u653e\u76f8\u5173\u901a\u8fc7PowerShell\u7684\u811a\u672c\u3002\u53c2\u7167\u524d\u51e0\u7bc7\u6587\u7ae0\u4e2d\u521b\u5efaPSNet\u7a0b\u5e8f\u96c6\u7684\u65b9\u6cd5\u548c\u76ee\u5f55\u7ed3\u6784\u521b\u5efaPSSecurity\u7a0b\u5e8f\u96c6\u76ee\u5f55\uff0c\u4fbf\u4e8e\u540e\u7eed\u5bf9\u7a0b\u5e8f\u96c6\u7684\u6269\u5c55\u3002<\/p>\n

\u5177\u4f53\u8be6\u7ec6\u7684\u6b65\u9aa4\u8bf7\u53c2\u89c1\u524d\u51e0\u7bc7\u6587\u7ae0\uff0c\u521b\u5efaPSSecurity\u7a0b\u5e8f\u96c6\u4e4b\u540e\u7684\u76ee\u5f55\u7ed3\u6784\u548c\u6587\u4ef6\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n

+D:\\MY DOCUMENTS\\WINDOWSPOWERSHELL\\MODULES
\u2514\u2500PSSecurity
    \u2502  PSSecurity.psm1
    \u2502 
    \u2514\u2500SQLServer
            Get-SqlSysLogin.ps1<\/p>\n

\u5728$Profile\u4e2d\u6dfb\u52a0\uff1a<\/p>\n

Import-Module $env:PSSpace\\PSSecurity  #\u7528\u4e8e\u5728PowerShell\u542f\u52a8\u65f6\u81ea\u52a8\u52a0\u8f7dPSSecurity\u7a0b\u5e8f\u96c6<\/p>\n

\u5176\u4e2dPSSecurity.psm1\u4e2d\u7684\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n

. $env:PSSpace\/PSSecurity\/SQLServer\/Get-SqlSysLogin.ps1 #\u5bfc\u5165Get-SqlSysLogin\u51fd\u6570<\/p>\n

Write-Host "PSSecurity Module Added" -BackgroundColor green -ForegroundColor blue #\u7528\u4e8e\u63d0\u793a\u6b64\u6a21\u5757\u5df2\u52a0\u8f7d
Export-ModuleMember -Function *  #\u7528\u4e8e\u5c06\u51fd\u6570\u5bfc\u51fa\u4e3a\u6a21\u5757\u6210\u5458<\/p>\n

\u63a5\u4e0b\u6765\u5c31\u662fGet-SqlSysLogin.ps1\u7684\u5185\u5bb9\u4e86<\/p>\n

        =====\u6587\u4ef6\u540d\uff1aGet-SqlSysLogin.ps1=====<\/span>\nfunction<\/font> Get-SqlSysLogin {\n\n  Param<\/font>(\n    [Parameter(Mandatory = $true,\n      Position = 0,\n      ValueFromPipeLine= $true)]\n    [Alias("PSComputerName"<\/font>,"CN"<\/font>,"MachineName"<\/font>,"IP"<\/font>,"IPAddress"<\/font>)]\n    [string]$ComputerName,\n    [parameter(Position = 1)]\n    [string]$UserName,\n    [parameter(Position = 2)]\n    [string]$Password\n  )\n  Process<\/font> {\n    $Connection = New-Object<\/font> System.Data.SQLClient.SQLConnection\n    if<\/font>($userName) {\n      $Connection.ConnectionString = "Data Source=$ComputerName;Initial Catalog=Master;User Id=$userName;Password=$password;"<\/font>\n    } else<\/font> {\n      $Connection.ConnectionString = "server=$computerName;Initial Catalog=Master;trusted_connection=true;"<\/font>\n    }\n    Try {\n      $Connection.Open()\n      $Command = New-Object<\/font> System.Data.SQLClient.SQLCommand #\u521b\u5efaSQLClient\u5bf9\u8c61\n      $Command.Connection = $Connection\n      $Command.CommandText = "SELECT * FROM master.SYS.syslogins"  <\/font>#\u4ecesyslogin\u8868\u8bfb\u53d6SQLServer\u767b\u5f55\u8d26\u6237\n      $Reader = $Command.ExecuteReader()\n      $Counter = $Reader.FieldCount\n      while<\/font> ($Reader.Read()) {\n        $SQLObject = @{}\n        for<\/font> ($i = 0; $i -lt<\/font> $Counter; $i++) {\n          $SQLObject.Add(\n            $Reader.GetName($i),\n            $Reader.GetValue($i)\n          );\n        }\n        # \u83b7\u53d6\u767b\u5f55\u7c7b\u578b\n        $type<\/font> = \n          if<\/font>($sqlObject.isntname -eq<\/font> 1) {\n            if<\/font>($sqlObject.isntgroup -eq<\/font> 1) {\n              "NT Group"<\/font>\n            } else<\/font> {\n              "NT User"<\/font>\n            }\n            } else<\/font> { \n              "SQL Server"<\/font>\n            }\n\n        New-Object<\/font> PSObject -Property @{\n          Name = $sqlObject.loginname;\n          Created = $sqlObject.createdate;\n          DenyLogin = [bool]$sqlObject.denylogin;\n          HasAccess =  [bool]$sqlObject.hasaccess;\n          Type<\/font> = $type<\/font>;\n          SysAdmin = [bool]$sqlObject.sysadmin;\n          SecurityAdmin = [bool]$sqlObject.securityadmin;\n          ServerAdmin = [bool][bool]$sqlObject.serveradmin;\n          SetupAdmin = [bool]$sqlObject.setupadmin;\n          ProcessAdmin = [bool]$sqlObject.processadmin;\n          DiskAdmin = [bool]$sqlObject.diskadmin;\n          DBCreator = [bool]$sqlObject.dbcreator;\n          NTUser = [bool]$sqlObject.isNTUser;\n          ComputerName = $ComputerName\n        } | Select-Object<\/font> Name, Created, Type<\/font>, DenyLogin, HasAccess, SysAdmin, SecurityAdmin, ServerAdmin, SetupAdmin, ProcessAdmin, DiskAdmin, DBCreator, NTUser, ComputerName\n      }\n      $Connection.Close()\n    }\n    Catch {\n      $error[0]\n    }\n  }\n}<\/pre>\n
 <\/p>\n
\u542f\u52a8PowerShell\u8fdb\u7a0b\uff0c\u53ef\u4ee5\u7528\u4e0b\u9762\u7684\u4e24\u79cd\u65b9\u5f0f\u8c03\u7528<\/p>\n
Get-SqlSysLogin -ComputerName SRV01 -UserName sa -Password sa #\u5355\u53f0\u4e3b\u673a\u767b\u5f55\u5c1d\u8bd5
\n  
"SQL01","SQL02","SQL03" | Get-SqlSysLogin -UserName sa -Password sa #\u591a\u53f0\u4e3b\u673a\u767b\u5f55\u5c1d\u8bd5<\/p>\n
\u5176\u4e2dComputerName\u4ee3\u8868sqlserver\u7684\u4e3b\u673a\u540d\u6216\u8005IP;UserName\u662f\u7528\u6237\u540d\uff0c\u5982\u679c\u4e0d\u586b\uff0c\u5219\u4f7f\u7528\u9ed8\u8ba4\u7684windows\u8eab\u4efd\u8ba4\u8bc1\uff0c\u5982\u679c\u4f7f\u7528windows\u8eab\u4efd\u8ba4\u8bc1\u5219\u9700\u8981\u786e\u4fdd\u5f53\u524d\u767b\u5f55\u5141\u8bb8\u53ef\u4ee5\u901a\u8fc7windows\u8eab\u4efd\u8ba4\u8bc1\u767b\u5f55\uff1bPassword\u4e0d\u7528\u8bf4\u5c31\u662f\u5bc6\u7801\u4e86\u3002<\/p>\n
PS C:\\Users\\fuhj> Get-SqlSysLogin -ComputerName **.**.**.** -UserName sa -Password *********** <\/p>\n
Name          : sa
\n  
Created       : 2003\/4\/8 9:10:35 <\/p>\n
Type          : SQL Server <\/p>\n
DenyLogin     : False <\/p>\n
HasAccess     : True <\/p>\n
SysAdmin      : True <\/p>\n
SecurityAdmin : False <\/p>\n
ServerAdmin   : False <\/p>\n
SetupAdmin    : False <\/p>\n
ProcessAdmin  : False <\/p>\n
DiskAdmin     : False <\/p>\n
DBCreator     : False <\/p>\n
NTUser        : False <\/p>\n
ComputerName  : **.**.**.** <\/p>\n
Name          : *****
\n  
Created       : 2011\/3\/14 8:31:44 <\/p>\n
Type          : SQL Server <\/p>\n
DenyLogin     : False <\/p>\n
HasAccess     : True <\/p>\n
SysAdmin      : False <\/p>\n
SecurityAdmin : False <\/p>\n
ServerAdmin   : False <\/p>\n
SetupAdmin    : False <\/p>\n
ProcessAdmin  : False <\/p>\n
DiskAdmin     : False <\/p>\n
DBCreator     : False <\/p>\n
NTUser        : False <\/p>\n
ComputerName  : **.**.**.**<\/p>\n
 \"Get-SqlSysLogin\" <\/p>\n
\u540e\u7eed\u601d\u8def\uff1a\u8fd9\u91cc\u662f\u5bf9\u5355\u53f0SQLServer\u670d\u52a1\u5668\u7684\u767b\u5f55\u5c1d\u8bd5\uff0c\u5982\u679c\u901a\u8fc7\u5bf9\u6307\u5b9a\u8303\u56f4\u7684IP\u8fdb\u884c\u7aef\u53e3\u7684\u626b\u63cf\u53d1\u73b0\u76f8\u5173\u7684SQLServer\u670d\u52a1\u5668\uff0c\u800c\u4e14\u80fd\u591f\u6709\u6bd4\u8f83\u9f50\u5168\u7684\u5b57\u5178\uff0c\u5bf9\u8fd9\u4e2a\u51fd\u6570\u8fdb\u884c\u6539\u9020\u5c31\u53ef\u4ee5\u5b57\u5178\u6a21\u5f0f\u66b4\u529b\u7834\u89e3SQLServer\u7684\u7528\u6237\u540d\u3001\u5bc6\u7801(\u6ce8\u610f\uff1a\u672c\u6587\u53ea\u63d0\u4f9b\u5b89\u5168\u653b\u9632\u7684\u601d\u8def\uff0c\u8bf7\u52ff\u5bf9\u4ed6\u4eba\u7cfb\u7edf\u8fdb\u884c\u66b4\u529b\u5c1d\u8bd5\uff0c\u5426\u5219\u540e\u679c\u7531\u653b\u51fb\u8005\u4e2a\u4eba\u81ea\u884c\u627f\u62c5<\/font>)\u3002<\/p>\n
\u672c\u6587\u521b\u5efa\u4e86PSSecurity\u5de5\u5177\u96c6\uff0c\u4ecb\u7ecd\u4e86\u901a\u8fc7PowerShell\u5c1d\u8bd5\u767b\u5f55SQLServer\u7684\u65b9\u6cd5\uff0c\u6b64\u79cd\u65b9\u6cd5\u53ef\u4ee5\u7528\u4e8e\u66b4\u529b\u7834\u89e3\u548c\u7a77\u4e3e\u8d26\u6237\u5bc6\u7801\uff0c\u7a77\u4e3e\u5c31\u9700\u8981\u4f9d\u9760\u6bd4\u8f83\u5168\u9762\u7684\u5b57\u5178\u7684\u652f\u6301\u4e86\u3002\u540e\u7eed\u7684\u6587\u7ae0\u4e2d\u5c06\u4f1a\u5206\u522b\u5bf9PSNet\u548cPSSecurity\u4e24\u4e2a\u5de5\u5177\u96c6\u8fdb\u884c\u6269\u5145\u548c\u5347\u7ea7\uff0c\u4f7f\u5176\u80fd\u9002\u5e94\u771f\u5b9e\u73af\u5883\u7684\u9700\u6c42\u3002<\/p>\n
 <\/p>\n
\u4f5c\u8005: \u4ed8\u6d77\u519b
\n  
\u51fa\u5904\uff1ahttp:\/\/blog.csdn.net\/fuhj02<\/a> <\/p>\n
\u7248\u6743\uff1a\u672c\u6587\u7248\u6743\u5f52\u4f5c\u8005\u548ccsdn\u5171\u6709 <\/p>\n
\u8f6c\u8f7d\uff1a\u6b22\u8fce\u8f6c\u8f7d\uff0c\u4e3a\u4e86\u4fdd\u5b58\u4f5c\u8005\u7684\u521b\u4f5c\u70ed\u60c5\uff0c\u8bf7\u6309\u8981\u6c42\u3010\u8f6c\u8f7d\u3011\uff0c\u8c22\u8c22 <\/p>\n
\u8981\u6c42\uff1a\u672a\u7ecf\u4f5c\u8005\u540c\u610f\uff0c\u5fc5\u987b\u4fdd\u7559\u6b64\u6bb5\u58f0\u660e\uff1b\u5fc5\u987b\u5728\u6587\u7ae0\u4e2d\u7ed9\u51fa\u539f\u6587\u8fde\u63a5\u4e14\u4fdd\u8bc1\u5185\u5bb9\u5b8c\u6574!\u5426\u5219\u5fc5\u7a76\u6cd5\u5f8b\u8d23\u4efb! <\/p>\n
\u4e2a\u4eba\u7f51\u7ad9: http:\/\/txj.shell.tor.hu\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      \u524d\u4e09\u7bc7\u6587\u7ae0\u4e2d\u521b\u5efa\u4e86PSNet\u7a0b\u5e8f\u96c6\uff0c\u5176\u4e2d\u5305\u542b\u4e86\u5bf9 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,50,1],"tags":[],"class_list":["post-33465","post","type-post","status-publish","format-standard","hentry","category-powershell","category-50","category-default"],"_links":{"self":[{"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=\/wp\/v2\/posts\/33465"}],"collection":[{"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=33465"}],"version-history":[{"count":1,"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=\/wp\/v2\/posts\/33465\/revisions"}],"predecessor-version":[{"id":33744,"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=\/wp\/v2\/posts\/33465\/revisions\/33744"}],"wp:attachment":[{"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=33465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=33465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fuhaijun.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=33465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}