Article
11/21/2014

Granting, Revoking, and Denying Permissions

The ServerPermission object is used to assign a set of permissions or an individual server permission to the ServerPermissionSet object. For server level permissions, the grantee refers to a logon. Logons authenticated by Windows are listed as Windows user names. When this code sample runs, it revokes the permission from the grantee and verifies it has been removed with the EnumServerPermissions method.

Database permissions and database object permissions can be assigned similarly by using the DatabasePermissionSet object and the ObjectPermissionSet object.

Example

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET or How to: Create a Visual C# SMO Project in Visual Studio .NET.

Granting Server Permissions in Visual Basic

This code example grants the Create Endpoint and Alter Any Endpoint permissions to the specified login, and then enumerates and displays the permissions. One of the permissions is revoked, and then the permissions are enumerated again. This example assumes that the specified login has the specified permissions to start with.

' 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

Granting Server Permissions in 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("");
   }
}

Granting Server Permissions in 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."
}
}

Granting, Revoking, and Denying Permissions

Example

Granting Server Permissions in Visual Basic

Granting Server Permissions in Visual C#

Granting Server Permissions in PowerShell

See Also

Concepts

Additional resources