Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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

, 23 Nov 2010
Rate this:
Please Sign up or sign in to vote.
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:

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.

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.

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

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.

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

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.

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.

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.

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.

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)

Share

About the Author

Kanasz Robert
Architect The Staffing Edge & 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

 
GeneralMy vote of 5 PinmemberSavalia Manoj M11-Jun-13 3:54 
GeneralRe: My vote of 5 PinmvpKanasz Robert11-Jun-13 23:18 
GeneralMy vote of 5 PinmemberTechnoGeek00129-Apr-13 2:58 
GeneralRe: My vote of 5 PinmvpKanasz Robert29-Apr-13 6:26 
QuestionGood Pinmemberxmaster123_212-Mar-13 23:55 
AnswerRe: Good PinmvpKanasz Robert13-Mar-13 3:15 
QuestionNice Pinmemberstrucker_luc18-Nov-12 3:14 
AnswerRe: Nice PinmvpKanasz Robert18-Nov-12 3:22 
QuestionInteresting article and very helpful Pinmemberkr1234564-Nov-12 3:55 
AnswerRe: Interesting article and very helpful PinmvpKanasz Robert4-Nov-12 4:04 
Questionnice one PinmembersuperdevX151-Nov-12 6:49 
AnswerRe: nice one PinmvpKanasz Robert1-Nov-12 6:56 
Questiongot my 5! Pinmemberhakon12331-Oct-12 5:28 
AnswerRe: got my 5! PinmvpKanasz Robert31-Oct-12 5:37 
QuestionExcellent Pinmembermemlon mulas29-Oct-12 5:13 
AnswerRe: Excellent PinmvpKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pinmemberjackhoal27-Oct-12 3:54 
AnswerRe: good and well written article PinmvpKanasz Robert27-Oct-12 4:01 
Questiongood one Pinmemberrobkaan27-Oct-12 3:25 
AnswerRe: good one PinmvpKanasz Robert27-Oct-12 3:31 
QuestionExcellent Pinmemberwindevvv21-Oct-12 6:47 
AnswerRe: Excellent PinmvpKanasz Robert21-Oct-12 7:01 
Questionwell done Pinmemberkaslaninovic2-Oct-12 22:46 
AnswerRe: well done PinmvpKanasz Robert3-Oct-12 6:52 
thank you
QuestionInteresting Pinmemberdeveloper88123-Sep-12 2:59 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 23 Nov 2010
Article Copyright 2010 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid