How to create Database User programmatically?






3.67/5 (5 votes)
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 Cons
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");