• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

How to change SQL Server DB Owner ?

Hi People,

I've got a several SQL Server database which is owned by DOMAIN\Administrator, I'd like  to change it to the respective person associated with their Application DB eg. DOMAIN\Person1

so how can I change the DB Owner for the database without causing problem or downtime for the production database in SQL Server 2008 and 2005 ?

Thanks.
0
Senior IT System Engineer
Asked:
Senior IT System Engineer
3 Solutions
 
Anthony PerkinsCommented:
Using T-SQL you can use:
exec sp_changedbowner 'UsernameGoesHere

Let me know if you think that is too much typing and you would rather click a mouse...
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
lol... anything that doesn't require downtime is good for me mate.

hopefully it can swap the DB Owner without needing restart or DB attach detach.
0
 
Anthony PerkinsCommented:
>>hopefully it can swap the DB Owner without needing restart or DB attach detach. <<
Not need to restart, let alone detach/attach.  It should take less that 5 seconds to complete and no one will be the wiser.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
kirkensCommented:

sp_changedbowner (This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER AUTHORIZATION instead.)
Link

Alter Authorization
Link2
0
 
Anthony PerkinsCommented:
Fair enough. So why don't you post the solution using ALTER AUTHORIZATION, rather than parroting what you read in SQL Server's BOL?
0
 
Scott PletcherSenior DBACommented:
Btw, be aware that for a few tasks SQL Server still requires that the user running the task be 'db_owner' (or sysadmin, of course).

So, check for any jobs being run using the old owner and make sure you change those to run as the new owner.  Hopefully there won't be any, but I would check first, just in case, or else you may get a job that fails due to insufficient rights.
0
 
Anthony PerkinsCommented:
Very good point.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now