SMO - 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);
}