How to create Database User programmatically?


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

One Response to How to create Database User programmatically?

  1. […] How to create Database User programmatically? « [Business Intelligence].[ALL] ramymahrous.wordpress.com/2009/11/03/how-to-create-database-user-programmatically – view page – cached 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… Read moreWell… 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 Read less […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: