권한 부여, 취소 및 거부

아티클
06/18/2013

ServerPermission 개체는 일련의 권한 또는 개별 서버 권한을 ServerPermissionSet 개체에 할당하는 데 사용됩니다. 서버 수준 권한의 경우 피부여자가 로그온을 참조합니다. Windows에 의해 인증된 로그온이 Windows 사용자 이름으로 나열됩니다. 이 코드 예제를 실행하면 피부여자로부터 권한이 취소되고 EnumServerPermissions 메서드로 권한이 제거되었는지 확인합니다.

DatabasePermissionSet 개체 및 ObjectPermissionSet 개체를 사용하여 비슷한 방법으로 데이터베이스 권한 및 데이터베이스 개체 권한을 할당할 수 있습니다.

예

제공된 코드 예제를 사용하려면 응용 프로그램을 만들 프로그래밍 환경, 프로그래밍 템플릿 및 프로그래밍 언어를 선택해야 합니다. 자세한 내용은 Visual Studio .NET에서 Visual Basic SMO 프로젝트 만들기 또는 Visual Studio .NET에서 Visual C# SMO 프로젝트 만들기를 참조하십시오.

Visual Basic에서 서버 권한 부여

이 코드 예제는 지정된 로그인에 Create Endpoint 및 Alter Any Endpoint 권한을 부여한 다음, 권한을 열거하고 표시합니다. 권한 중 하나를 취소하고 나서 권한을 다시 열거합니다. 이 예에서는 지정된 로그인에 지정된 시작 권한이 있다고 가정합니다.

' compile with: /r:Microsoft.SqlServer.Smo.dll /r:Microsoft.SqlServer.ConnectionInfo.dll 
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll /r:Microsoft.SqlServer.SqlEnum.dll
Imports Microsoft.SqlServer.Management.Smo

Public Class A
   Public Shared Sub Main()
      Dim svr As New Server()

      ' Creating the logins (Grantee)
      Dim vGrantee As [String] = "Grantee1"
      Dim login As New Login(svr, vGrantee)
      login.LoginType = LoginType.SqlLogin
      login.Create("password@1")

      Dim vGrantee2 As [String] = "Grantee2"
      Dim login2 As New Login(svr, vGrantee2)
      login2.LoginType = LoginType.SqlLogin
      login2.Create("password@2")

      ' Define a ServerPermissionSet that contains permission to Create Endpoint and Alter Any Endpoint. 
      Dim sps As New ServerPermissionSet(ServerPermission.CreateEndpoint)
      sps.Add(ServerPermission.AlterAnyEndpoint)

      ' Grant Create Endpoint and Alter Any Endpoint permissions to Grantee
      svr.Grant(sps, vGrantee)
      svr.Grant(sps, vGrantee2)

      ' Enumerate and display the server permissions in the set for the grantee specified in the vGrantee string variable. 
      Dim spis As ServerPermissionInfo() = svr.EnumServerPermissions(vGrantee, sps)
      'enumerates all server permissions for the Grantee from the specified permission set
      Console.WriteLine("===========Before revoke==================")
      For Each spi As ServerPermissionInfo In spis
         Console.WriteLine(spi.Grantee + " has " & spi.PermissionType.ToString() & " permission.")
      Next
      Console.WriteLine(" ")

      ' Revoke the create endpoint permission from the grantee. 
      svr.Revoke(New ServerPermissionSet(ServerPermission.CreateEndpoint), vGrantee)

      ' Enumerate and display the server permissions in the set for the grantee specified in the vGrantee string variable. 
      spis = svr.EnumServerPermissions(vGrantee, sps)

      Console.WriteLine("=========After revoke================")
      For Each spi As ServerPermissionInfo In spis
         Console.WriteLine(spi.Grantee + " has " & spi.PermissionType.ToString() & " permission.")
      Next
      Console.WriteLine(" ")

      ' Grant the Create Server Role permission to the grantee. 
      svr.Grant(New ServerPermissionSet(ServerPermission.ViewAnyDatabase), vGrantee)
      ' Enumerate and display the server permissions for the grantee specified in the vGrantee string variable. 

      ' enumerates all server permissions for the Grantee
      spis = svr.EnumServerPermissions(vGrantee)

      Console.WriteLine("=========After grant===============")

      For Each spi As ServerPermissionInfo In spis
         Console.WriteLine(spi.Grantee + " has " & spi.PermissionType.ToString() & " permission.")
      Next
      Console.WriteLine("")

      ' Enumerate and display the server permissions in the set for all logins. 
      spis = svr.EnumServerPermissions(sps)
      'enumerates all server permissions in the set for all logins
      Console.WriteLine("=========After grant===============")

      For Each spi As ServerPermissionInfo In spis
         Console.WriteLine(spi.Grantee + " has " & spi.PermissionType.ToString() & " permission.")
      Next
      Console.WriteLine("")
   End Sub
End Class

Visual C#에서 서버 권한 부여

// compile with: /r:Microsoft.SqlServer.Smo.dll /r:Microsoft.SqlServer.ConnectionInfo.dll 
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll /r:Microsoft.SqlServer.SqlEnum.dll
using System;
using Microsoft.SqlServer.Management.Smo;

public class A {
   public static void Main() {
      Server svr = new Server();

      // Creating the logins (Grantee)
      String vGrantee = "Grantee1";
      Login login = new Login(svr, vGrantee);
      login.LoginType = LoginType.SqlLogin;
      login.Create("password@1");

      String vGrantee2 = "Grantee2";
      Login login2 = new Login(svr, vGrantee2);
      login2.LoginType = LoginType.SqlLogin;
      login2.Create("password@2");

      // Define a ServerPermissionSet that contains permission to Create Endpoint and Alter Any Endpoint. 
      ServerPermissionSet sps = new ServerPermissionSet(ServerPermission.CreateEndpoint);
      sps.Add(ServerPermission.AlterAnyEndpoint);

      // Grant Create Endpoint and Alter Any Endpoint permissions to Grantee
      svr.Grant(sps, vGrantee);
      svr.Grant(sps, vGrantee2);

      // Enumerate and display the server permissions in the set for the grantee specified in the vGrantee string variable. 
      ServerPermissionInfo[] spis = svr.EnumServerPermissions(vGrantee, sps); //enumerates all server permissions for the Grantee from the specified permission set

      Console.WriteLine("===========Before revoke==================");
      foreach (ServerPermissionInfo spi in spis) {
         Console.WriteLine(spi.Grantee + " has " + spi.PermissionType.ToString() + " permission.");
      }
      Console.WriteLine(" ");

      // Revoke the create endpoint permission from the grantee. 
      svr.Revoke(new ServerPermissionSet(ServerPermission.CreateEndpoint), vGrantee);

      // Enumerate and display the server permissions in the set for the grantee specified in the vGrantee string variable. 
      spis = svr.EnumServerPermissions(vGrantee, sps);

      Console.WriteLine("=========After revoke================");
      foreach (ServerPermissionInfo spi in spis) {
         Console.WriteLine(spi.Grantee + " has " + spi.PermissionType.ToString() + " permission.");
      }
      Console.WriteLine(" ");

      // Grant the Create Server Role permission to the grantee. 
      svr.Grant(new ServerPermissionSet(ServerPermission.ViewAnyDatabase), vGrantee);
      // Enumerate and display the server permissions for the grantee specified in the vGrantee string variable. 

      // enumerates all server permissions for the Grantee
      spis = svr.EnumServerPermissions(vGrantee); 

      Console.WriteLine("=========After grant===============");

      foreach (ServerPermissionInfo spi in spis) {
         Console.WriteLine(spi.Grantee + " has " + spi.PermissionType.ToString() + " permission.");
      }
      Console.WriteLine("");

      // Enumerate and display the server permissions in the set for all logins. 
      spis = svr.EnumServerPermissions(sps); //enumerates all server permissions in the set for all logins

      Console.WriteLine("=========After grant===============");

      foreach (ServerPermissionInfo spi in spis) {
         Console.WriteLine(spi.Grantee + " has " + spi.PermissionType.ToString() + " permission.");
      }
      Console.WriteLine("");
   }
}

PowerShell에서 서버 권한 부여

# Set the path context to the local, default instance of SQL Server.
CD \sql\localhost\
$srv = get-item default

#The subject login:
# \"Place Login Name here - has permission to Create Endpoints\"
$vGrantee = "LoginName"

#This sample assumes that the grantee already has permission to Create Endpoints. 

$sps  =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.ServerPermissionSet

$sps.CreateEndpoint = $true
$sps.AlterAnyEndpoint = $true

#This sample assumes that the grantee already has permission to Create Endpoints. 

#Enumerate and display the server permissions in the set for the grantee specified
# in the vGrantee string variable.
$spis = $srv.EnumServerPermissions($vGrantee)

"=================Before revoke==========================="
foreach ( $spi in $spis)
{
    $spi.Grantee + " has " + $spi.PermissionType + " permission."
}
""
#remove perission to create an endpoint
$sps.CreateEndpoint = $false
$srv.Revoke($sps, $vGrantee)

#Enumerate and display the server permissions in the set for the grantee specified
# in the vGrantee string variable.
$spis = $srv.EnumServerPermissions($vGrantee)

"=================After revoke==========================="
foreach ( $spi in $spis)
{
    $spi.Grantee + " has " + $spi.PermissionType + " permission."
}
""
#Grant the revoked permissions back
$sps.CreateEndpoint = $true
$sps.AlterAnyEndpoint = $true
$srv.Grant($sps, $vGrantee)

#Enumerate and display the server permissions in the set for the grantee specified
# in the vGrantee string variable.
$spis = $srv.EnumServerPermissions($vGrantee)

"=================After grant==========================="
foreach ( $spi in $spis)
{
    $spi.Grantee + " has " + $spi.PermissionType + " permission."
}
}

참고 항목

개념

사용 권한 계층(데이터베이스 엔진)

다음을 통해 공유

권한 부여, 취소 및 거부

예

Visual Basic에서 서버 권한 부여

Visual C#에서 서버 권한 부여

PowerShell에서 서버 권한 부여

참고 항목

개념

추가 리소스