How to create Database User programmatically?

Sign Up to vote

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Well... we need to assign a server login to has access on a database, let's say the SQL Server Login we had created programmatically should has access on database "Database1" and for some reasons we should do that programmatically Well. Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, and Microsoft.SqlServer.ConnectionInfo

/// <summary>
/// Creates database user
/// </summary>
private void CreateDatabaseUser(Database database, String username)
{
    // initializes new User object and we say to which database it belongs
    // and its name
    User sqlServerUser = new User(datawarehouseDatabase, username); 
    sqlServerUser .UserType = UserType.SqlLogin; //SqlLogin not anything else
    //associated the user to login name, login name should be valid login name
    sqlServerUser .Login = "login_name"; 
    // here's we create the user on the database and till now the user
    // don't have any permission on database objects
    sqlServerUser .Create();
    //or any role like db_databasereader, db_databasewriter,...
    sqlServerUser .AddToRole("db_owner"); 
}

P.S: There's catastrophic mistake in Microsoft documentation of User.Login as they documented it as ReadOnly field although it's not.

How we can call the above method?

Server sqlInstance = new Server(
new Microsoft.SqlServer.Management.Common.ServerConnection(
new System.Data.SqlClient.SqlConnection("."))); //connects to the local server
//initialize new object from database  adventureworks
Database database = sqlInstance.Databases["adventureworks"]; 
//creates user RamyMahrous on database adventureworks
CreateDatabaseUser(database, "RamyMahrous");