How to identify SQL Server job owner

I was trying to remove a user from SQL Server database box but while trying to remove the user, I got below error:














Now in order to identify where the user has create a SQL Server job, you can run below query to get the job details owned by the user. Here we go:

Select Name from msdb.dbo.sysjobs
WHERE owner_sid IN 
(
SELECT sid
FROM MASTER.sys.syslogins
WHERE Name = 'kirankr'
)

Once you get the job details, you have to remove the maintenance plan of that job from SQL Server Management Studio > Server Node > Management > Maintenance Plans > Get the plan name> Right click it > Delete . After successful removal of dependent job, you can delete the user from the database.Hope you like this post. If yes, do comment and share the post to those who needs it.

Read More

How to Get Collection Folder Location in SCCM

Your manager rushes to you and asks you to identify the Folder location of a culprit collection. You goes to SCCM console and search for it either with name or collection ID. You get the half detail but your manager asked for folder location and you scratches your head because from console you cant identify the folder location of that collection.

Dont worry!! running below query, you can get the folder detail quickly. Here we go:

Open SQL Server Management Studio > Access SCCM Database(preferably CAS) > New query > Run below query 

select c.SiteID as 'Collection ID',c.CollectionName,f.Name 

as 'Folder Name', f.FolderPath from vCollections c

inner join FolderMembers fm on fm.InstanceKey=c.SiteID

inner join folders f on f.ContainerNodeID=fm.ContainerNodeID

Output looks like below:

Read More