1

I just want to know is that anyway we can check in SQL server if anybody change the db_owner of a database to other login ID

Improve this question
1
  • If its set to the wrong user then fix it and keep an eye on it. Only someone with dba/sysadmin creds could do this. So if you're concerned i suggest you audit permissions and revoke unnecesary privs. Set up some logging and if it happens again.. dust off your DBA-god-ban-hammer and smite them.
    – Sir Swears-a-lot
    Commented Mar 28, 2017 at 8:31

2 Answers 2

Reset to default
1

It is recorded in your default trace. This article ( https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/ )explains in details how to query default trace. It does not tell you who did it and what was the previous owner. But will tell you what time it happened.

Improve this answer
1

Are you running any DMV capture tools like sp_whoisactive, redgate, DPA, etc or had tracing enabled? That will make it easier to see if it catches the SQL that ran making the change.

What I would do now is set up a trigger that will track the aspects of who makes permission changes to your database and log those transactions for future review.

Example of DDL Trigger.

Improve this answer
1
  • +1 DDL trigger. Audit. Wait. Watch. Pounce. Smite with the wrath of a vengeful God.
    – Sir Swears-a-lot
    Commented Mar 28, 2017 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.