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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Individual Ownership of Objects

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-sql
USE [YourDatabaseNameHere]
GO
ALTER 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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -