February 25, 2004 at 7:03 am
I'm running SQL 2000 Enterprise and am looking at setting the database ownership along with all its objects to a defined user name, not dbo. This is a pre-existing database.
To date, I've created the user named 'test', changed all object ownership to test, granted the user 'test' the dbo role. The database was originally created by sa and is defined as 'the owner' when you call up database properties in sql enterprise.
And this is where my problem is. When I run sp_changedbowner 'test', it states that the user is already a user in the database and the ownership change fails. I can't drop the user because it owns objects in the database.
I feel that granting the database owner role is sufficient but the end-user is uncomfortable that the third party app insists that the user 'test' be the owner of the db.
Any suggestions how I can manage this change.
thanks
tom
February 25, 2004 at 11:47 am
1. Change all object ownership to dbo
2. Drop user 'test' from database
3. Change database owner to 'test'
User 'test' will be the aliased as dbo so all the objects will be owned by it.
Greg
February 26, 2004 at 5:16 pm
You can also just alias the user directly as dbo, take a look at sp_addalias.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply