object ownership issue

[mkal]

Windows 2003 x64 sp2
SQL Server 2000 x86 sp4

I found a sql login that had been given sysadmin rights to the server. It was also an user in a number of databases as well with db_owner permissions.

I removed the sysadmin rights and now whenever someone creates a data object (table, sproc, view...) the owner is the not dbo but the sql login. The login still has dbo permissions in the database but something is amisss...is this even possible?

What if anything can I do to fix it. Dropping and recreating the user is not an option as we have to many critical applications that use this login.

Thanks

 

[mrdenny]

This is normal and by design. In order to create objects owner by another user (in this case the dbo user) the objects have to be specifically created under the dbo ownership.

A workaround is to make the user the owner of the database instead of simply giving them dbo rights within the database.

This is done with the sp_changedbowner system stored procedure.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[mkal]

Let's say there is a sql login = 'mkal' and that login is a sysadmin and in the db_owner group for a database called 'test'. However, the owner of the database is 'sa' not 'mkal'.

If I am removed from the sysadmin group but not the database role db_owner. When I go to create a table called 'users' using the syntax below:

CREATE TABLE users (firstname varchar(25), lastnamne varchar(50))

Then the owner would be mkal.users not dbo.users? Because that's what is happening.

Thanks

 

[mrdenny]

That is correct.

That is the expected and designed result.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog