Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2013-02-26 : 08:14:54
|
| Hi all,How do you prevent prorammers and DBAs from becoming the owner of jobs and databases in your instances? As we all know, that becomes a problem when the person leaves the company and you want to remove their domain account.Thanks, Jack |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-26 : 13:41:18
|
If you are using Windows Authentication, the default schema may be the username. Objects are (usually) owned by a schema. When a user creates an object, unless they explicitly specify the schema, their default schema owns the objects.Ways to fix it are:a) Insist that when users create objects, they create it by specifying a schema name - for example, "CREATE PROCEDURE dbo.MyNewStoredProc" rather than just "CREATE PROCEDURE MyNewStoredProc"or,b) Change the default schema of users to dbo or some other schema. You can do this from SSMS object explorer by right-clicking on the user name under Databasename -> Security. Or, you can use t-sqlUSE [YourDatabaseNameHere]GOALTER USER [UserName] WITH DEFAULT_SCHEMA=[dbo]GO For existing objects, you can use ALTER AUTHORIZATION, for example:ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada; http://msdn.microsoft.com/en-us/library/ms187359.aspx |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2013-02-26 : 14:17:27
|
| Thanks for your suggestions, James.All of the stored procedures and tables are under schema dbo so that's not a problem.I was referring to ownership of databases and jobs.I can change the owner of course. But I'm looking for tips for how other DBAs prevent it initially. They need to be able to create jobs - but how can I configure so that the owner is a generic account of my choosing?Obviously, I'm having some difficulty in "enforcing" our verbal policy.Thanks, Jack |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-02-26 : 14:41:37
|
This query will identify any database not owned by sa:select name from sys.databases where owner_sid<>0x01 And the following will generate the commands needed to change the owner to sa:select 'exec ' + quotename(name) + '..sp_changedbowner ''sa'' ' from sys.databases where owner_sid<>0x01 Copy and paste the results into a new query window to execute. |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2013-02-26 : 16:24:25
|
| Thanks, Robvolk.Do you recommend having sa as the owner of all databases? I have a mix of sa and a few that are owned by the domain account that the SQl Server server runs under.If sa is the owner, will any issues occur if I disable the sa login?Thanks, Jack |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-02-26 : 21:55:17
|
| I don't think disabling sa affects it, but don't take my word for it, test it with another login. I find it easier to make all databases owned by sa because I know it will always be there. |
|
|
|
|
|
|
|
|
Individual Ownership of Objects