SMO - Server Permissions

SMO allows you to grant, deny and revoke server permissions to and from SQL Server login account. ServerPermissionInfo object captures the set of server permission returned by EnumServerPermissions() method. You can pass the name of SQL Server login as parameter to this method. Than you will get permissions for this login.

ServerPermissionSet object represents a set of SQL Server permissions you want to grant, deny or revoke.

Server permissions are required when granting, denying, or revoking server-level permissions on an instance of SQL Server. The ServerPermission object is used to specify the set of permissions that apply to the Grant, Deny, and Revoke methods of the Server object. Also, server permissions can be added to the ServerPermissionSet object, which can also be used with the Deny, Revoke, and Grant methods.

ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    ServerPermissionSet sps;
    sps = new ServerPermissionSet(ServerPermission.CreateAnyDatabase);
    srv.Grant(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);
    srv.Deny(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);
    srv.Revoke(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("slimak"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}