Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Let's Talk Ownership (and SQL Jobs)


Let's Talk Ownership (and SQL Jobs)

Author
Message
RSP
RSP
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 623
Comments posted to this topic are about the item Let's Talk Ownership (and SQL Jobs)



Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1207 Visits: 3662
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
DBA in Unit 7
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 1124
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
RSP
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 623
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
Jerry Hung
SSC Eights!
SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)

Group: General Forum Members
Points: 946 Visits: 1208
This runs beautifully with the Central Management Servers
I fixed all maintenance plans and jobs on 20+ servers in 1 run Smile


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



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 2204
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
UMG Developer
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 2204
Jerry Hung (11/19/2009)I fixed all maintenance plans and jobs on 20+ servers in 1 run Smile


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
tracy_d82
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 0
who knew it was that simple!

Tracy
prance_43
prance_43
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
see i think it is more complicated instead of easy! there is no simple form to the code?

Peter
simon.murin
simon.murin
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 575
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')

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search