Let's Talk Ownership (and SQL Jobs)

Question

Let's Talk Ownership (and SQL Jobs)

RSP

SSCrazy

Points: 2614
More actions
November 19, 2009 at 12:19 am

#216823

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Rich Mechaber SSChampion Points: 11130 More actions · Answer 1

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..sysjobs

WHERE owner_sid<>suser_sid('sa')

Rich Mechaber

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 2

Thanks for sharing.

Just wanna add one note about the code for

changing the Maintenance plan ownership :

use msdb;

go

update dbo.sysssispackages

set ownersid = (

select sid from msdb.sys.syslogins where name = 'sa')

where [name] = 'MaintenancePlanNameHere';

It only works for SQL2008 because there is no dbo.sysssispackages in the MSDB in SQL2005. Use dbo.sysdtspackages90 in SQL 2005.

RSP SSCrazy Points: 2614 More actions · Answer 3

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

This runs beautifully with the Central Management Servers

I fixed all maintenance plans and jobs on 20+ servers in 1 run 🙂

--get the owners of the maintenance plans

IF @@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'

END

ELSE

BEGIN

SELECT name

,description

,SUSER_SNAME(ownersid)

FROM msdb.dbo.sysdtspackages90-- sql 2005

END

--get the owners of the maintenance plans

IF @@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' ;

END

ELSE

BEGIN

UPDATE msdb.dbo.sysdtspackages90

SET ownersid = SUSER_SID('sa')

WHERE SUSER_SNAME(ownersid) IN ('jerryhung')

--AND [name] = 'MaintenancePlanNameHere' ;

END

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

UMG Developer SSChampion Points: 13482 More actions · Answer 5

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.

UMG Developer SSChampion Points: 13482 More actions · Answer 6

Jerry Hung (11/19/2009)I fixed all maintenance plans and jobs on 20+ servers in 1 run 🙂

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.

tracy_d82 SSC Journeyman Points: 79 More actions · Answer 7

tracy_d82

SSC Journeyman

Points: 79

November 19, 2009 at 12:19 pm

#1081770

who knew it was that simple!

Tracy

prance_43 Valued Member Points: 69 More actions · Answer 8

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

Peter[/url]

simon.murin SSC-Addicted Points: 449 More actions · Answer 9

I do agree the msdb.sys.syslogins shouldn't be used.

Another way is to use the SUSER_SID function:

SQL 2005 Maintenance Plans

UPDATE

[msdb].[dbo].[sysdtspackages90]

SET

[ownersid] = SUSER_SID('sa')

SQL 2008 Maintenance Plans

UPDATE

[msdb].[dbo].[sysssispackages]

SET

[ownersid] = SUSER_SID('sa')

Rob Fisk SSCommitted Points: 1861 More actions · Answer 10

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.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Rob Fisk (11/24/2009)
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.

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).

Tom