Granting, Revoking, and Denying Permissions
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
SQL database in Microsoft Fabric
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 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#
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
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
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.
# 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 permission 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."
}
}