SQLServerCentral / Discuss Content Posted by Robert Pearl / Article Discussions / Article Discussions by Author / Let's Talk Ownership (and SQL Jobs) / Latest Posts

RE: Let's Talk Ownership (and SQL Jobs)

L' Eomot Inversé — Sat, 23 Jan 2010 12:59:54 GMT

[quote][b]Rob Fisk (11/24/2009)[/b][hr]Yeah. I'd be really worried about the security implications of this.I thought it was a great article highlighting the problems that can arise from personally owned jobs.[/quote]Pity it didn't highlight the problems that can arise from SA owned jobs as well. I really don't fancy having everything running with sysadmin privileges, whether it needs them or not.In fact I'm fairly tempted to say that no jobs at all should be owned by SA because (although you may want interdomain connections without having interdomain trust at NT level so that you have to have SQL logins as well as NT logins) there's no good reason why SA should ever be be able to login (it's easy to have a job that creates a random SA password and doesn't put it where any human can get it).

RE: Let's Talk Ownership (and SQL Jobs)

Rob Fisk — Tue, 24 Nov 2009 03:05:18 GMT

Yeah. I'd be really worried about the security implications of this.I thought it was a great article highlighting the problems that can arise from personally owned jobs.Between it and the comments made I now have all the building blocks to write a job ownership transfer that can be run at point of departure of one user to transfer job ownership to the replacement.

RE: Let's Talk Ownership (and SQL Jobs)

simon.murin — Thu, 19 Nov 2009 16:18:01 GMT

I do agree the msdb.sys.syslogins shouldn't be used.Another way is to use the SUSER_SID function:SQL 2005 Maintenance Plans[code="plain"]UPDATE[msdb].[dbo].[sysdtspackages90]SET[ownersid] = SUSER_SID('sa')[/code]SQL 2008 Maintenance Plans[code="plain"]UPDATE[msdb].[dbo].[sysssispackages]SET[ownersid] = SUSER_SID('sa')[/code]

RE: Let's Talk Ownership (and SQL Jobs)

prance_43 — Thu, 19 Nov 2009 13:08:06 GMT

see i think it is more complicated instead of easy! there is no simple form to the code?

RE: Let's Talk Ownership (and SQL Jobs)

tracy_d82 — Thu, 19 Nov 2009 12:19:46 GMT

who knew it was that simple!

RE: Let's Talk Ownership (and SQL Jobs)

UMG Developer — Thu, 19 Nov 2009 10:20:45 GMT

[quote][b]Jerry Hung (11/19/2009)[/b]I fixed all maintenance plans and jobs on 20+ servers in 1 run :)[/quote]Not quite that easy, I think you still have to manually change, save, change back, and save each maintenance plan for the owner change to be 100% complete.

RE: Let's Talk Ownership (and SQL Jobs)

UMG Developer — Thu, 19 Nov 2009 10:14:38 GMT

You really want all jobs owned by SA? Doesn't that sort of eliminate any idea of security? If a developer wants to change something they don't have permission for they just have to create a job to do it or to give themselves the permission to do it.I agree that maintenance plans and the like should be owned by SA, or another generic account with SA permissions, but I certainly won't be changing all of the jobs on our SQL servers to be owned by SA.

RE: Let's Talk Ownership (and SQL Jobs)

Jerry Hung — Thu, 19 Nov 2009 09:48:13 GMT

This runs beautifully with the Central Management ServersI fixed all maintenance plans and jobs on 20+ servers in 1 run :)[code]--get the owners of the maintenance plansIF @@VERSION LIKE '%SQL SERVER 2008%' --SELECT @@VERSION BEGIN SELECT NAME ,description ,SUSER_SNAME(ownersid) --,* FROM msdb.dbo.sysssispackages -- sql 2008 WHERE SUSER_SNAME(ownersid) <> 'sa' ENDELSE BEGIN SELECT name ,description ,SUSER_SNAME(ownersid) FROM msdb.dbo.sysdtspackages90 -- sql 2005 END--get the owners of the maintenance plansIF @@VERSION LIKE '%SQL SERVER 2008%' --SELECT @@VERSION BEGIN UPDATE msdb.dbo.sysssispackages SET ownersid = SUSER_SID('sa') WHERE SUSER_SNAME(ownersid) IN ('jerryhung') --AND [name] = 'MaintenancePlanNameHere' ; ENDELSE BEGIN UPDATE msdb.dbo.sysdtspackages90 SET ownersid = SUSER_SID('sa') WHERE SUSER_SNAME(ownersid) IN ('jerryhung') --AND [name] = 'MaintenancePlanNameHere' ; END[/code]

RE: Let's Talk Ownership (and SQL Jobs)

RSP — Thu, 19 Nov 2009 09:35:09 GMT

Hi, Max: Thanks for pointing that out, b/c as a matter of fact, I included with the submission for both SQL 2005 and SQL 2008. I thought you could scroll to the SQL2K5 script, but guess it was omitted. (Steve?)Thanks all for your current (and future) comments!- RP

RE: Let's Talk Ownership (and SQL Jobs)

DBA in Unit 7 — Thu, 19 Nov 2009 09:29:04 GMT

Thanks for sharing. Just wanna add one note about the code for changing the Maintenance plan ownership :[code="sql"]use msdb;goupdate dbo.sysssispackagesset ownersid = (select sid from msdb.sys.syslogins where name = 'sa')where [name] = 'MaintenancePlanNameHere';[/code]It only works for SQL2008 because there is no dbo.sysssispackages in the MSDB in SQL2005. Use dbo.sysdtspackages90 in SQL 2005.

RE: Let's Talk Ownership (and SQL Jobs)

rmechaber — Thu, 19 Nov 2009 08:57:08 GMT

Great, thanks!I created a query to look for jobs not owned by SA, and discovered a small "gotcha". A LEFT JOIN would work, of course, but I don't really need it.-- Jobs not owned by sa.-- N.B.! DO NOT JOIN to Master.dbo.syslogins as some websites suggest.-- Using Windows integrated security means that the job owner may be an individual without a specific login, if the login-- was created for a Windows group. -- For example, when I create jobs, the owner defaults to OURDOMAIN\myusername, but there is no corresponding login.SELECT suser_sname(owner_sid) as OwnerName, *FROM msdb..sysjobsWHERE owner_sid<>suser_sid('sa')Rich Mechaber

Let's Talk Ownership (and SQL Jobs)

RSP — Thu, 19 Nov 2009 00:19:46 GMT

Comments posted to this topic are about the item [B]Let's Talk Ownership (and SQL Jobs)[/B]