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
private void CreateDatabaseUser(Database database, String username)
{
User sqlServerUser = new User(datawarehouseDatabase, username);
sqlServerUser .UserType = UserType.SqlLogin;
sqlServerUser .Login = "login_name";
sqlServerUser .Create();
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(".")));
Database database = sqlInstance.Databases["adventureworks"];
CreateDatabaseUser(database, "RamyMahrous");