How to create Database User programmatically?

November 3, 2009

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)

{

User sqlServerUser = new User(datawarehouseDatabase, username); //initializes new User object and we say to which database it belongs and its name

sqlServerUser .UserType = UserType.SqlLogin; //SqlLogin not anything else

sqlServerUser .Login = “login_name”; //associated the user to login name, login name should be valid login name

sqlServerUser .Create();//here’s we create the user on the database and till now the user don’t have any permission on database objects

sqlServerUser .AddToRole(“db_owner”); //or any role like db_databasereader, db_databasewriter,…

}

P.S: There’s catastrophic mistake in Microsoft documentation of User.Login as they documented it as ReadOnly field although it’s not.sqlloginHow 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

Database database = sqlInstance.Databases[“adventureworks”]; //initialize new object from database adventureworks

CreateDatabaseUser(database, “RamyMahrous”); //creates user RamyMahrous on database adventureworks


How to create SQL Server Login programmatically?

April 28, 2009

I got this question from Daniweb C# forum, and of course when I need to do something against SQL Server object from .net I go to SMO (SQL Server Management Objects) which provides a great functionalities to do anything with SQL Server instead of going on SSMS and create some scripts then embedding them into stored procedure then call it…. off…

Anyway to avoid showing how much I’m talkative…

Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo
and Microsoft.SqlServer.SqlEnum

Server sqlServerInstance = new Server(
new Microsoft.SqlServer.Management.Common.ServerConnection(
new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=Master;Integrated Security=True")));// your connection string I place mine for illustration..
Login loginObj = new Login(sqlServerInstance, @"DomainName\UserName");
loginObj.DefaultDatabase = "Master";
loginObj.LoginType = LoginType.WindowsUser;
loginObj.Enable();
loginObj.Create("password"); //set the password
//there're many properties to do some tasks related to Login object...

If you used LoginType.WindowsUser, be sure to provide valid windows username
and if you aren’t on Domain use the machine name instead.

If you need to create SQL login use LoginType.SqlLogin…

You can explore Login class more on http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.create.aspx