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.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
Database database = sqlInstance.Databases[“adventureworks”]; //initialize new object from database adventureworks
CreateDatabaseUser(database, “RamyMahrous”); //creates user RamyMahrous on database adventureworks