creating / changing owner problem

[geoff]

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_addlogi n '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQ uery();

//grant login access to new database to new owner
tmpCon = new SqlConnection(" Data Source=ourserve r;User
ID=uid;Password =pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOC N;");
tmpcmd = new SqlCommand("sp_ grantdbaccess '" + username + "'",tmpCon) ;
tmpCon.Open();
tmpcmd.ExecuteN onQuery();
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=ourserve r;User
ID=uid;Password =pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOC N;");
tmpcmd = new SqlCommand("sp_ changedbowner
'HolderUserDoNo tDelete'",tmpCo n);
tmpCon.Open();
tmpcmd.ExecuteN onQuery();
tmpcmd.CommandT ext = "sp_changedbown er '" + username + "'";
tmpcmd.ExecuteN onQuery();
tmpcmd.Dispose( ); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose( ); tmpCon = null;

[Erland Sommarskog]

Re: creating / changing owner problem

geoff (cakewalkr7@hot mail.com) writes:[color=blue]
> 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.[/color]

If you intend to make the login owner of the database, there is no
reason to create a user for him in the database, but just skip that
step. Once the login owns the database, he also has a user in it.

Note: A login in SQL Server is server-wide. A user is something in a
database, which can be connected to a login. The username and loginname
may be the same, but they don't have to.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp