SQL Server Performance Forum – Threads Archive
sp_changedbowner Advice
Hi, Some databases that I support have owner of the user that created them originally instead of sa. These databases are production and have applications running against them. My question is what problems if any may I come across if I use sp_changedbowner to change the owner to sa for these databases (i.e could it cause probs with the app or anything else ?) Thanks in advance for any help. PS – Great forum !!A best practice is to have dbo own all objects in a database. This not only makes managing security easier, it can also speed up SQL Server’s performance. Changing the owner of an object may or may not have an effect on your application. It depends on how the application is written. If the code in the app refers to the object owner, then you will have problems, and need to changes the app’s code. But if the app does not refer to the owner of the object, just to the object name itself, then making your change will not cause you any negative effects. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Thanks for the quick reply, Here is an example of what I am trying to do : Database name = TESTDB
DBO = testuser Currently testuser is the dbo for the database TESTDB as the database was created by this user, but sa as default has db_owner privs for all databases. Therefore am I right in thinking that if I use "sp_changedbowner ‘sa’" on the TESTDB database, that sa will become the dbo for all objects and it should cause no probs. The TESTDB owner can not have been used / hardcoded within the app as the vendors did not know of this user before the app was installed. Cheers Richard
Anyone got any replies to my second post ?
Essentially, yes. ‘sa’ is a login though – dbo is a database user. Users are linked to logins but aren’t one and the same. So even if testuser is dbo, you can still login as ‘sa’ with system administrator rights and do CREATE TABLE dbo.table which will be dbo-owned. It’s preferable I believe to have sa (or equivalent) as dbo. Tom Pullen
DBA, Oxfam GB
]]>