Skip to content

managing-users-roles-and-logins.md

Latest commit

 

History

History
230 lines (180 loc) · 10.5 KB

managing-users-roles-and-logins.md

File metadata and controls

230 lines (180 loc) · 10.5 KB
title Managing Users, Roles, and Logins | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology
ms.topic reference
helpviewer_keywords
logins [SMO]
roles [SMO]
users [SMO]
ms.assetid 74e411fa-74ed-49ec-ab58-68c250f2280e
author stevestein
ms.author sstein
manager craigg

Managing Users, Roles, and Logins

In SMO, logins are represented by the xref:Microsoft.SqlServer.Management.Smo.Login object. When the logon exists in [!INCLUDEssNoVersion], it can be added to a server role. The server role is represented by the xref:Microsoft.SqlServer.Management.Smo.ServerRole object. The database role is represented by the xref:Microsoft.SqlServer.Management.Smo.DatabaseRole object and the application role is represented by the xref:Microsoft.SqlServer.Management.Smo.ApplicationRole object.

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

Every xref:Microsoft.SqlServer.Management.Smo.Database object has a xref:Microsoft.SqlServer.Management.Smo.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 xref:Microsoft.SqlServer.Management.Smo.Login object's xref:Microsoft.SqlServer.Management.Smo.Login.EnumDatabaseMappings%2A method can be used to list all users in every database that is associated with the logon. Alternatively, the xref:Microsoft.SqlServer.Management.Smo.User object's xref:Microsoft.SqlServer.Management.Smo.Login property specifies the logon that is associated with the user.

[!INCLUDEssNoVersion] 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.

Example

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 Create a Visual Basic SMO Project in Visual Studio .NET and Create a Visual C# SMO Project in Visual Studio .NET.

Enumerating Logins and Associated Users in Visual Basic

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 xref:Microsoft.SqlServer.Management.Smo.Login.EnumDatabaseMappings%2A method of the xref:Microsoft.SqlServer.Management.Smo.Login object to list all the database users who are associated with the logon. The example creates a logon and user in the [!INCLUDEssSampleDBnormal] database to make sure there is mapping information to enumerate.

Enumerating Logins and Associated Users in Visual C#

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 xref:Microsoft.SqlServer.Management.Smo.Login.EnumDatabaseMappings%2A method of the xref:Microsoft.SqlServer.Management.Smo.Login object to list all the database users who are associated with the logon. The example creates a logon and user in the [!INCLUDEssSampleDBnormal] database to make sure there is mapping information to enumerate.

{   
Server srv = new Server();   
//Iterate through each database and display.   
  
foreach ( Database 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;  
   d = db.EnumLoginMappings();   
   //Display the mapping information.   
   foreach (DataRow r in d.Rows) {   
      foreach (DataColumn c in r.Table.Columns) {   
         Console.WriteLine(c.ColumnName + " = " + r[c]);   
      }   
      Console.WriteLine(" ");   
   }   
}   
}

Enumerating Logins and Associated Users in PowerShell

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 xref:Microsoft.SqlServer.Management.Smo.Login.EnumDatabaseMappings%2A method of the xref:Microsoft.SqlServer.Management.Smo.Login object to list all the database users who are associated with the logon. The example creates a logon and user in the [!INCLUDEssSampleDBnormal] database to make sure there is mapping information to enumerate.

# Set the path context to the local, default instance of SQL Server.  
CD \sql\localhost\Default\Databases  
  
#Iterate through all databases  
 foreach ($db in Get-ChildItem)  
 {  
 "====="  
 "Login Mappings for the database: "+ $db.Name  
  
 #get the datatable containing the mapping from the smo database object  
 $dt = $db.EnumLoginMappings()  
  
 #display the results  
 foreach($row in $dt.Rows)  
     {  
        foreach($col in $row.Table.Columns)  
      {  
        $col.ColumnName + "=" + $row[$col]  
       }
     }  
 }

Managing Roles and Users

This sample demonstrates how to how to manage roles and users. The first sample uses C#, the second Visual Basic. These samples need to reference the following assemblies:

  • Microsoft.SqlServer.Smo.dll

  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

  • Microsoft.SqlServer.ConnectionInfo.dll

  • Microsoft.SqlServer.SqlEnum.dll

using Microsoft.SqlServer.Management.Smo;  
using System;  
  
public class A {  
   public static void Main() {  
      Server svr = new Server();  
      Database db = new Database(svr, "TESTDB");  
      db.Create();  
  
      // Creating Logins  
      Login login = new Login(svr, "Login1");  
      login.LoginType = LoginType.SqlLogin;  
      login.Create("password@1");  
  
      Login login2 = new Login(svr, "Login2");  
      login2.LoginType = LoginType.SqlLogin;  
      login2.Create("password@1");  
  
      // Creating Users in the database for the logins created  
      User user1 = new User(db, "User1");  
      user1.Login = "Login1";  
      user1.Create();  
  
      User user2 = new User(db, "User2");  
      user2.Login = "Login2";  
      user2.Create();  
  
      // Creating database permission Sets  
      DatabasePermissionSet dbPermSet = new DatabasePermissionSet(DatabasePermission.AlterAnySchema);  
      dbPermSet.Add(DatabasePermission.AlterAnyUser);  
  
      DatabasePermissionSet dbPermSet2 = new DatabasePermissionSet(DatabasePermission.CreateType);  
      dbPermSet2.Add(DatabasePermission.CreateSchema);  
      dbPermSet2.Add(DatabasePermission.CreateTable);  
  
      // Creating Database roles  
      DatabaseRole role1 = new DatabaseRole(db, "Role1");  
      role1.Create();  
  
      DatabaseRole role2 = new DatabaseRole(db, "Role2");  
      role2.Create();  
  
      // Granting Database Permission Sets to Roles  
      db.Grant(dbPermSet, role1.Name);  
      db.Grant(dbPermSet2, role2.Name);  
  
      // Adding members (Users / Roles) to Role  
      role1.AddMember("User1");  
  
      role2.AddMember("User2");  
  
      // Role1 becomes a member of Role2  
      role2.AddMember("Role1");  
  
      // Enumerating through explicit permissions granted to Role1  
      // enumerates all database permissions for the Grantee  
      DatabasePermissionInfo[] dbPermsRole1 = db.EnumDatabasePermissions("Role1");     
      foreach (DatabasePermissionInfo dbp in dbPermsRole1) {  
         Console.WriteLine(dbp.Grantee + " has " + dbp.PermissionType.ToString() + " permission.");  
      }  
      Console.WriteLine(" ");  
   }  
}

This is the Visual Basic version:

Imports Microsoft.SqlServer.Management.Smo  
  
Public Class A  
   Public Shared Sub Main()  
      Dim svr As New Server()  
      Dim db As New Database(svr, "TESTDB")  
      db.Create()  
  
      ' Creating Logins  
      Dim login As New Login(svr, "Login1")  
      login.LoginType = LoginType.SqlLogin  
      login.Create("password@1")  
  
      Dim login2 As New Login(svr, "Login2")  
      login2.LoginType = LoginType.SqlLogin  
      login2.Create("password@1")  
  
      ' Creating Users in the database for the logins created  
      Dim user1 As New User(db, "User1")  
      user1.Login = "Login1"  
      user1.Create()  
  
      Dim user2 As New User(db, "User2")  
      user2.Login = "Login2"  
      user2.Create()  
  
      ' Creating database permission Sets  
      Dim dbPermSet As New DatabasePermissionSet(DatabasePermission.AlterAnySchema)  
      dbPermSet.Add(DatabasePermission.AlterAnyUser)  
  
      Dim dbPermSet2 As New DatabasePermissionSet(DatabasePermission.CreateType)  
      dbPermSet2.Add(DatabasePermission.CreateSchema)  
      dbPermSet2.Add(DatabasePermission.CreateTable)  
  
      ' Creating Database roles  
      Dim role1 As New DatabaseRole(db, "Role1")  
      role1.Create()  
  
      Dim role2 As New DatabaseRole(db, "Role2")  
      role2.Create()  
  
      ' Granting Database Permission Sets to Roles  
      db.Grant(dbPermSet, role1.Name)  
      db.Grant(dbPermSet2, role2.Name)  
  
      ' Adding members (Users / Roles) to Role  
      role1.AddMember("User1")  
  
      role2.AddMember("User2")  
  
      ' Role1 becomes a member of Role2  
      role2.AddMember("Role1")  
  
      ' Enumerating through explicit permissions granted to Role1  
      ' enumerates all database permissions for the Grantee  
      Dim dbPermsRole1 As DatabasePermissionInfo() = db.EnumDatabasePermissions("Role1")  
      For Each dbp As DatabasePermissionInfo In dbPermsRole1  
         Console.WriteLine(dbp.Grantee + " has " & dbp.PermissionType.ToString() & " permission.")  
      Next  
      Console.WriteLine(" ")  
   End Sub  
End Class