Create Login and User in SQL Server 2005 from ASP.NET or C#

I have seen many times that people used to use DML and DDL command of SQL Server with SQL Connection and SQL Command from C# but since they have never tried other T-SQL command they are unaware with how to do that? This is not something different than other DDL and DML command. .NET Framework 3.5 is pretty much powerful to handle almost every script of SQL Server. Let us see how to create User Login in SQL Server with ASP.NET C# code.

I have one webform with one button and it will call one method to which used to do the job of creating user in SQL Server.

//button1's click event which will call CreateSQLLogin method

//and will create Login in SQL Server and User for AdventureWorks db

protected void Button1_Click(object sender, EventArgs e)

{

string strConn = "Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa";

string strLoginName = "TestUser";

string strPassword = "testing123";

int i=CreateSQLLogin(strConn, strLoginName, strPassword);

if (i == 1)

{

Response.Write("Created Successfully!!!!");

}

else

{

Response.Write("problem in creating account");

}

}

//create login and user

public int CreateSQLLogin(string strConn, string strLoginName, string strPassword)

{

string strCreateLogin = @"use master CREATE LOGIN [" + strLoginName + "] WITH PASSWORD=N'" + strPassword + "' , DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON";

string strCreateUser = @"use AdventureWorks CREATE User[" + strLoginName + "] FOR LOGIN ["+ strLoginName +"]";

try

{

System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(strConn);

sqlConn.Open();

System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(strCreateLogin, sqlConn);

sqlCmd.ExecuteNonQuery();

System.Data.SqlClient.SqlCommand sqlCmd1 = new System.Data.SqlClient.SqlCommand(strCreateUser, sqlConn);

sqlCmd1.ExecuteNonQuery();

return 1;

}

catch

{

return 0;

}

}

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com