授與、撤銷和拒絕權限

ServerPermission 物件可用於將一組權限或個別的伺服器權限指派給 ServerPermissionSet 物件。如果是伺服器層級權限，被授與者是指登入。Windows 驗證過的登入會列為 Windows 使用者名稱。當這個程式碼範例執行時，會從被授與者撤銷權限，並使用 EnumServerPermissions 方法確認該權限已經移除。

資料庫權限和資料庫物件權限也可藉由 DatabasePermissionSet 物件和 ObjectPermissionSet 物件，以類似的方式進行指派。

範例

如果要使用所提供的任何程式碼範例，您必須選擇建立應用程式用的程式設計環境、程式設計範本及程式設計語言。如需詳細資訊，請參閱＜如何：在 Visual Studio .NET 中建立 Visual Basic SMO 專案＞或＜如何：在 Visual Studio .NET 中建立 Visual C# SMO 專案＞。

在 Visual Basic 中授與伺服器權限

此程式碼範例會將「建立端點」和「改變任何端點」權限授與指定的登入，然後再列舉和顯示權限。其中一個權限會被撤銷，然後再次列舉權限。這個範例假設指定的登入一開始即具有指定的權限。

' 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."
}
}

請參閱

概念

權限階層 (Database Engine)