SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Individual Ownership of Objects
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

393 Posts

Posted - 02/26/2013 :  08:14:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/26/2013 :  13:41:18  Show Profile  Reply with Quote
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

393 Posts

Posted - 02/26/2013 :  14:17:27  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 02/26/2013 :  14:41:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

393 Posts

Posted - 02/26/2013 :  16:24:25  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 02/26/2013 :  21:55:17  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000