Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

SMO Tutorial 2 of n - (Programming Objects Not Used for Data Storage)

Rate me:
Please Sign up or sign in to vote.
4.95/5 (67 votes)
23 Nov 2010CPOL4 min read 84.2K   76   37
In this second part of the tutorial, I will show you how to create objects not used for data storage (logins, users, roles ...) using SMO

Table of contents

Introduction

This article is part 2 of a series of articles about programming Server Management Objects. In the first article, I have described what Server Management Objects are. I have shown how to work with database storage objects.

Background

In this article, I will describe how to work with objects not used for data storage. To get started with SMO, first you must add references to Visual Studio. In Add Reference, window select:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Mircorost.SqlServer.ConnectionInfo

When the references are added, you must add 2 using statements for two namespaces:

C#
Using Microsoft.SqlServer.Management.Common;
Using Microsoft.SqlServer.Management.Smo;

Logins

Class Login represents login of SQL Server. Using instance of Login class, login can be added, deleted to or from server roles. Server object includes Logins properties that represents all SQL Server logins. Login can be associated with user in more than one database.

Enumerating SQL Server Logins

The following code demonstrates how to enumerate SQL Server logins and users associated with login.

C#
ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);

    foreach (Login login in srv.Logins)
    {
        Console.WriteLine(login.Name);
        if (login.EnumDatabaseMappings() != null)
        {
            foreach (DatabaseMapping map in login.EnumDatabaseMappings())
            {
                Console.WriteLine(" Database: " + map.DBName);
                Console.WriteLine(" User: " + map.UserName);
            }
        }

    }
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

EnumDatabaseMappings property is a list of DatabaseMapping objects that represent mapping to database and database user.

Creating SQL Server Login

Login class can be used for creation of new database login. Login class has a constructor with two parameters. The first parameter specifies database server represented by Server object and the second is login name. Method AddToRole() adds login to a SQL Server role. The following example shows how to create new SQL Server login called "NewLogin" with password pwd and adds it to the sysadmin server role.

C#
ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Login login = new Login(srv, "NewLogin");
    login.LoginType = LoginType.SqlLogin;
    login.Create("pwd");
    login.AddToRole("sysadmin");
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

Users

Database users are represented by Users property of Database object. This example enumerates the users of database "AdventureWorks". User object represents SQL Server security principal used for controlling access permissions with a database. When SQL Server login is created and mapped to the database, user object is created and added into Users collection.

Enumerating Database Users

C#
ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];
    foreach(User user in db.Users)
    {
        Console.WriteLine("User: " + user.Name);
        Console.WriteLine("Login: " + user.Login);
        Console.WriteLine("Type: " + user.UserType);
    }
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

Creating Database Users

The following example demonstrates how to create a database user. User object represents a new user. User class has a constructor with two parameters. The first parameter specifies a database object and the second specifies name of new login. Login property represents valid SQL Server login for user.

C#
ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];
    User u = new User(db, "NewUser");
    u.Login = "kanasz";
    u.Create();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

Assigning Role to Database User

C#
ServerConnection conn = 
	new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];
    User u = db.Users["NewUser"];
    u.AddToRole("db_owner");
    u.Alter();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

Roles

Roles of the database are represented by Roles property of Database object. Roles property is a collection of DatabaseRole objects. Using this collection is possible to enumerate all database roles. For each DatabaseRole object, you can get all members of this role calling EnumMembers() method.

Enumerating Database Roles

The following example demonstrates how to enumerate database roles and members for each role.

C#
ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];
    foreach (DatabaseRole dr in db.Roles)
    {
        Console.WriteLine("Name: " + dr.Name);
        Console.WriteLine("CreateDate: " + dr.CreateDate);
        Console.WriteLine("Owner: " + dr.Owner);
        Console.WriteLine("Role members:");
        foreach (string s in dr.EnumMembers())
            Console.WriteLine("  " + s);
        Console.WriteLine();
    }

}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

Creating New Database Role

For creating a new database role, it is important to create a new DatabaseRole object. Constructor of this object has two parameters that specifies Database object and name of new database role. When properties are set, Create() method creates a new database role. The following example demonstrates how to create a new database role and assign user "NewUser" to this role.

C#
ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];
    DatabaseRole dbRole = new DatabaseRole(db,"db_newRole");
    dbRole.Create();
    User u = db.Users["NewUser"];
    u.AddToRole("db_newRole");

}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

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.

C#
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);
}

.NET Framework Assemblies

Microsoft SQL Server now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming. SQL Server includes the .NET Framework version 2.0 SP1 pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

The following example demonstrates how you can enumerate .NET Framework assemblies defined in "AdventureWorks" database. Assembly property of Database object represents collection of SqlAssemblies objects. Every assembly can contains more than one file. When you want to get a list of assembly files, you have to use SqlAssemblyFiles property of SqlAssembly objects.

C#
ServerConnection conn = new ServerConnection(@"ANANAS\ANANAS2009", "kanasz", "chaaron");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];

    foreach (SqlAssembly assembly in db.Assemblies)
    {
        Console.WriteLine("Assembly name: " + " " + assembly.Name);
        foreach (SqlAssemblyFile assemblyFile in assembly.SqlAssemblyFiles)
            Console.WriteLine("  " + assemblyFile.Name);
    }

}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

History

  • 21 Nov 2010 - Article posted

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist

Open source projects: DBScripter - Library for scripting SQL Server database objects


Please, do not forget vote

Comments and Discussions

 
QuestionVery good article Pin
t.alkahtiri15-Dec-14 3:01
t.alkahtiri15-Dec-14 3:01 
GeneralMy vote of 5 Pin
Savalia Manoj M11-Jun-13 3:54
Savalia Manoj M11-Jun-13 3:54 
GeneralRe: My vote of 5 Pin
Kanasz Robert11-Jun-13 23:18
professionalKanasz Robert11-Jun-13 23:18 
GeneralMy vote of 5 Pin
npdev1329-Apr-13 2:58
npdev1329-Apr-13 2:58 
GeneralRe: My vote of 5 Pin
Kanasz Robert29-Apr-13 6:26
professionalKanasz Robert29-Apr-13 6:26 
QuestionGood Pin
xmaster123_212-Mar-13 23:55
xmaster123_212-Mar-13 23:55 
AnswerRe: Good Pin
Kanasz Robert13-Mar-13 3:15
professionalKanasz Robert13-Mar-13 3:15 
QuestionNice Pin
strucker_luc18-Nov-12 3:14
strucker_luc18-Nov-12 3:14 
AnswerRe: Nice Pin
Kanasz Robert18-Nov-12 3:22
professionalKanasz Robert18-Nov-12 3:22 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:55
kr1234564-Nov-12 3:55 
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:04
professionalKanasz Robert4-Nov-12 4:04 
Questionnice one Pin
superdevX151-Nov-12 6:49
superdevX151-Nov-12 6:49 
AnswerRe: nice one Pin
Kanasz Robert1-Nov-12 6:56
professionalKanasz Robert1-Nov-12 6:56 
Questiongot my 5! Pin
hakon12331-Oct-12 5:28
hakon12331-Oct-12 5:28 
AnswerRe: got my 5! Pin
Kanasz Robert31-Oct-12 5:37
professionalKanasz Robert31-Oct-12 5:37 
QuestionExcellent Pin
memlon mulas29-Oct-12 5:13
memlon mulas29-Oct-12 5:13 
AnswerRe: Excellent Pin
Kanasz Robert29-Oct-12 5:19
professionalKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:54
jackhoal27-Oct-12 3:54 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:01
professionalKanasz Robert27-Oct-12 4:01 
Questiongood one Pin
robkaan27-Oct-12 3:25
robkaan27-Oct-12 3:25 
AnswerRe: good one Pin
Kanasz Robert27-Oct-12 3:31
professionalKanasz Robert27-Oct-12 3:31 
QuestionExcellent Pin
windevvv21-Oct-12 6:47
windevvv21-Oct-12 6:47 
AnswerRe: Excellent Pin
Kanasz Robert21-Oct-12 7:01
professionalKanasz Robert21-Oct-12 7:01 
Questionwell done Pin
kaslaninovic2-Oct-12 22:46
kaslaninovic2-Oct-12 22:46 
AnswerRe: well done Pin
Kanasz Robert3-Oct-12 6:52
professionalKanasz Robert3-Oct-12 6:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.