How to: Grant Server Permissions in Visual Basic .NET

This section describes how to grant server permissions to a server logon by using Visual Basic .NET.

The code example grants the Create Endpoint and Alter Any Endpoint permissions the specified logon, 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 logon has the specified permissions to start with.

Granting and revoking server permissions to a login

1. Start Visual Studio 2005.

2. From the File menu, select New Project. The New Project dialog box appears.

3. In the Project Types pane, select Visual Basic. In the Templates pane, select Console Application.

4. (Optional) In the Name box, type the name of the new application.

5. Click OK to load the Visual Basic console application template.

6. On the Project menu, select Add Reference item. The Add Reference dialog box appears. Select Browse and locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:

   Microsoft.SqlServer.ConnectionInfo.dll

   Microsoft.SqlServer.Smo.dll

   Microsoft.SqlServer.SqlEnum.dll

   Microsoft.SqlServer.SmoEnum.dll

7. On the View menu, click Code.-Or-Select the Module1.vb window to display the code window.

8. In the code, before any declarations, type the following Imports statements to qualify the types in the SMO namespace:

   Imports Microsoft.SqlServer.Management.Smo
   Imports Microsoft.SqlServer.Management.Common
   

9. Insert the code that follows this procedure into the main program.

10. Run and build the application.

Example

'Connect to the local, default instance of SQL Server.
Dim svr As Server
svr = New Server()
'Define a ServerPermissionSet that contains permission to Create Endpoint and Alter Any Endpoint.
Dim sps As ServerPermissionSet
sps = New ServerPermissionSet(ServerPermission.CreateEndpoint)
sps.Add(ServerPermission.AlterAnyEndpoint)
'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.
Dim spis As ServerPermissionInfo()
spis = svr.EnumServerPermissions(vGrantee, sps)
Dim spi As ServerPermissionInfo
Console.WriteLine("=================Before revoke===========================")
For Each spi In spis
    Console.WriteLine(spi.Grantee & " has " & spi.PermissionType.ToString & " permission.")
Next
Console.WriteLine(" ")
'Remove a permission from the set.
sps.Remove(ServerPermission.CreateEndpoint)
'Revoke the create endpoint permission from the grantee.
svr.Revoke(sps, 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 In spis
    Console.WriteLine(spi.Grantee & " has " & spi.PermissionType.ToString & " permission.")
Next
Console.WriteLine(" ")
'Grant the Create Endpoint permission to the grantee.
svr.Grant(sps, 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 grant=============================")
For Each spi In spis
    Console.WriteLine(spi.Grantee & " has " & spi.PermissionType.ToString & " permission.")
Next
Console.WriteLine("")

See Also

Concepts

Granting, Revoking, and Denying Permissions

Help and Information

Getting SQL Server 2005 Assistance