SQL script to list particular domain login and objects it owns ?

Question

Hello, have a particular domain login within SQL Server and would like to disable it but would like to be safe first and check if it owns any objects to avoid breaking anything.

Is there a script that can list all objects like DBs, Jobs, etc... that it owns for me to review?

Thanks in advance.

Answer 1

Databases:
SELECT * FROM sys.databases WHERE owner_sid = suser_sid('DOMAIN\user')

Jobs. Hm, that should be in msdb.dbo.sysjobs, but I need to check the documentation to give you the exact query. You or I do the googling?

Linked servers do not have owners.

Then there are a lot of more things that have owners, not the least on database level. sys.objects.owner_principal_id (again, working from memory, you may want to check Docs) can give you this. But then there are sys.certificates, sys.asymmetric_keys and the list just goes on.

The good news is that you cannot drop a user than owns something.

And the same is true on server level: you cannot drop login that owns something. With the exception of things that are in msdb, so job ownership is important to check. And possibly other things you can use in msdb.

Then again, you were only looking into disabling the login, not dropping it. And in that case, there is no issue with ownership. A disabled login can own both this and that.

(And I am sorry, if I am referring you to the Docs rather than giving you answer right off the bat. But as I said, either you or I would have to look it up. And you are the one who has the need...)