Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Let's Talk Ownership (and SQL Jobs) Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 12:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:36 AM
Points: 172, Visits: 567
Comments posted to this topic are about the item Let's Talk Ownership (and SQL Jobs)


Post #821381
Posted Thursday, November 19, 2009 8:57 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:18 AM
Points: 717, Visits: 3,035
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
Post #821696
Posted Thursday, November 19, 2009 9:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 11:18 AM
Points: 196, Visits: 1,088
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.
Post #821730
Posted Thursday, November 19, 2009 9:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:36 AM
Points: 172, Visits: 567
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



Post #821734
Posted Thursday, November 19, 2009 9:48 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 775, Visits: 1,202
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



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #821751
Posted Thursday, November 19, 2009 10:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #821780
Posted Thursday, November 19, 2009 10:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #821785
Posted Thursday, November 19, 2009 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 19, 2009 12:18 PM
Points: 1, Visits: 0
who knew it was that simple!

Tracy
Post #821867
Posted Thursday, November 19, 2009 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 19, 2009 1:03 PM
Points: 1, Visits: 0
see i think it is more complicated instead of easy! there is no simple form to the code?

Peter
Post #821914
Posted Thursday, November 19, 2009 4:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 13, 2014 3:56 PM
Points: 77, Visits: 571
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')
Post #822008
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse