SQL Clone
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
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

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



Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 3670
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-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 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
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1806 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
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3012 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
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3012 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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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

Tracy
prance_43
prance_43
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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