Artigo
04/01/2012

Concedendo, revogando e negando permissões

O objeto ServerPermission é usado para atribuir um conjunto de permissões ou uma permissão de servidor individual ao objeto ServerPermissionSet. Para permissões em nível de servidor, o usuário autorizado se refere a um logon. Logons autenticados pelo Windows são listados como nomes de usuários do Windows. Quando essa amostra de código é executada, ela revoga a permissão do usuário autorizado e verifica se ela foi removida com o método EnumServerPermissions.

Permissões do banco de dados e permissões do objeto de banco de dados podem ser atribuídas similarmente usando os objetos DatabasePermissionSet e ObjectPermissionSet.

Exemplo

Para usar qualquer exemplo de código fornecido, será necessário escolher o ambiente de programação, o modelo de programação e a linguagem de programação para criar o seu aplicativo. Para obter mais informações, consulte Como criar um projeto SMO do Visual Basic no Visual Studio .NET ou Como criar um projeto SMO do Visual C# no Visual Studio .NET.

Concedendo permissões de servidor no Visual Basic

Este exemplo de código concede as permissões Criar Ponto de Extremidade e Alterar Qualquer Ponto de Extremidade ao logon especificado e, em seguida, enumera e exibe as permissões. Uma das permissões é revogada e, depois, as permissões são enumeradas novamente. Esse exemplo supõe que o logon especificado já tem as permissões especificadas.

' 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

Concedendo permissões de servidor no 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("");
   }
}

Concedendo permissões de servidor no 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."
}
}

Consulte também

Conceitos

Hierarquia de permissões (Mecanismo de Banco de Dados)

Share via