I'm trying to create a new database and new login to allow a client
(through my asp.net page) to create their own database, username and
password. I've tried using the stored procedures I've found in BOL,
but I can't get it to work right. It keeps saying that I can't assign
this username as the db owner since it's already a user for the
database. I then read other posts about reassigning the db owner to
another dummy account and then trying to reassign it to the new one,
but that isn't working either. Can anyone look at my code and tell me
what I'm doing wrong? Thanks.
//create db login
qry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
//grant login access to new database to new owner
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;
//connect to new database under sa account and change owner to new
account
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_changedbowner
'HolderUserDoNotDelete'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.CommandText = "sp_changedbowner '" + username + "'";
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;
