Presents your 
SQL SERVER E-NEWSLETTER for October 15, 2002

<------------------------------------------->

Designate the DB_OWNER

When restoring or creating databases, have you noticed that the 
database owner is your NT authenticated name? To keep consistency 
among databases, you may want to consider using the system procedure, 
sp_changedbowner, to change the database owner to a user like the 
system administrator (SA). You may consider writing a script that 
traverses all the user databases and reassigns the database owner 
to SA.

The system procedure, sp_changedbowner, has a parameter, @map, that if
omitted when executed, defaults to a null value and will remap aliases 
of the old database owner to the new database owner, SA.

To demonstrate, let's first create a database that should be as small 
as the model system database and run the command, sp_helpuser, to get a
listing of newly created users.

CREATE DATABASE test
GO
USE test
GO
EXEC sp_helpuser
GO

This should list the database owner (db_owner) as the login you used in
SQL Server. If you used Windows NT authentication, then there should be 
a NULL LoginName and a value for the SID.

Next, let's add a couple of logins, ISUser1 and ISUser2. Let's 
designate ISUser1 as an alias to db_owner. Let's change the database owner to SA
as well.

EXEC sp_addlogin @loginame = 'ISUser1', @passwd = 'ISUser1', @defdb = 
'master'
EXEC sp_addlogin @loginame = 'ISUser2', @passwd = 'ISUser2', @defdb = 
'master'
EXEC sp_addalias @loginame = 'ISUser1', @name_in_db = 'dbo'

EXEC sp_changedbowner @loginame = 'sa', @map = 'TRUE'

EXEC sp_helpuser

The output should show SA as the db_owner and ISUser1 as a user that is
aliased to db_owner.

Now we will designate ISUser2 as the new database owner using
sp_changedbowner. We will use the @map parameter for the procedure and 
assign it to
a false value that will drop users that are aliases.

EXEC sp_changedbowner @loginame = 'ISUser2', @map = 'FALSE'

EXEC sp_helpuser
GO

The output should show that ISUser2 is now the new database owner and
that ISUser1 has been dropped. Next, we need to drop the test database 
to
wrap up this demonstration.

USE master
GO
DROP DATABASE test
GO

----------------------------------------