Export (0) Print
Expand All

Managing Users, Roles, and Logins

In SMO, logins are represented by the Login object. When the logon exists in SQL Server, it can be added to a server role. The server role is represented by the ServerRole object. The database role is represented by the DatabaseRole object and the application role is represented by the ApplicationRole object.

Privileges associated with the server level are listed as properties of the ServerPermission object. The server level privileges can be granted to, denied to, or revoked from individual logon accounts.

Every Database object has a UserCollection object that specifies all users in the database. Each user is associated with a logon. One logon can be associated with users in more than one database. The Login object's EnumDatabaseMappings method can be used to list all users in every database that is associated with the logon. Alternatively, the User object's Login property specifies the logon that is associated with the user.

SQL Server databases also have roles that specify a set of database level privileges that let a user perform specific tasks. Unlike server roles, database roles are not fixed. They can be created, modified, and removed. Privileges and users can be assigned to a database role for bulk administration.

For the following code example, you will have to select the programming environment, programming template and the programming language to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET and How to: Create a Visual C# SMO Project in Visual Studio .NET.

Every user in a database is associated with a logon. The logon can be associated with users in more than one database. The code example shows how to call the EnumDatabaseMappings method of the Login object to list all the database users who are associated with the logon. The example creates a logon and user in the AdventureWorks database to make sure there is mapping information to enumerate.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Iterate through each database and display.
Dim db As Database
For Each db In srv.Databases
    Console.WriteLine("============================================")
    Console.WriteLine("Login Mappings for the database: " + db.Name)
    Console.WriteLine(" ")
    'Run the EnumLoginMappings method and return details of database user-login mappings to a DataTable object variable.
    Dim d As DataTable
    d = db.EnumLoginMappings
    'Display the mapping information.
    Dim r As DataRow
    Dim c As DataColumn
    For Each r In d.Rows
        For Each c In r.Table.Columns
            Console.WriteLine(c.ColumnName + " = " + r(c))
        Next
        Console.WriteLine(" ")
    Next
Next


Every user in a database is associated with a logon. The logon can be associated with users in more than one database. The code example shows how to call the EnumDatabaseMappings method of the Login object to list all the database users who are associated with the logon. The example creates a logon and user in the AdventureWorks database to make sure there is mapping information to enumerate.

//Connect to the local, default instance of SQL Server. 
{ 
Server srv = default(Server); 
srv = new Server(); 
//Iterate through each database and display. 
Database db = default(Database); 
foreach ( db in srv.Databases) { 
   Console.WriteLine("===================================="); 
   Console.WriteLine("Login Mappings for the database: " + db.Name); 
   Console.WriteLine(" "); 
   //Run the EnumLoginMappings method and return details of database user-login mappings to a DataTable object variable. 
   DataTable d = default(DataTable); 
   d = db.EnumLoginMappings; 
   //Display the mapping information. 
   DataRow r = default(DataRow); 
   DataColumn c = default(DataColumn); 
   foreach ( r in d.Rows) { 
      foreach ( c in r.Table.Columns) { 
         Console.WriteLine(c.ColumnName + " = " + r(c)); 
      } 
      Console.WriteLine(" "); 
   } 
} 
} 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft