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
