Hello, in our company's product, we attach a database to SQL Server, and we need to add credentials to some accounts to the catalog.
Currently we're using SMO to do this, but this gives us problems (x86 vs x64, SQL Server 2005 vs 2008, etc)
Is there's anyway of adding a login (to the server) and a user (to the catalog) using SQL?
This is the current code to add login:
connection = new ServerConnection(new SqlConnection(this.connectionString));
Server server = new Server(connection);
//Drop existing login (if exist)
Login login = server.Logins[AccountName];
if (login != null)
login.Drop();
//Create new login
login = new Login(server, AccountName);
login.LoginType = LoginType.WindowsUser;
login.Create();
DatabaseMapping mapping = new DatabaseMapping(login.Name, connection.DatabaseName, login.Name);
and to add user:
connection = new ServerConnection(new SqlConnection(this.connectionString));
Server server = new Server(connection);
Microsoft.SqlServer.Management.Smo.Database database = server.Databases[CatalogName];
User user = server.Databases[CatalogName].Users[LoginName];
if (user != null)
user.Drop();
user = new User(database, LoginName);
user.Login = LoginName;
user.Create();
user.AddToRole("db_owner");
I want to perform these operations using SQL. Any ideas?