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