Solved

SQL Jobs - Users that can create/modify/delete

Posted on 2014-11-18
4
141 Views
Last Modified: 2014-11-18
Is there a script to tell me which of my sql instance users have the ability to create, modify, and delete SQL agent jobs?
0
Comment
Question by:holemania
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 40450047
;WITH RoleMembers (member_principal_id, role_principal_id)
AS
(
  SELECT
   rm1.member_principal_id,
   rm1.role_principal_id
  FROM sys.database_role_members rm1 (NOLOCK)
   UNION ALL
  SELECT
   d.member_principal_id,
   rm.role_principal_id
  FROM sys.database_role_members rm (NOLOCK)
   INNER JOIN RoleMembers AS d
   ON rm.member_principal_id = d.role_principal_id
)
select distinct rp.name as database_role, mp.name as database_user
from RoleMembers drm
  join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
  join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
WHERE rp.name = 'SQLAgentUserRole'
0
 

Author Comment

by:holemania
ID: 40450086
Thanks Aneesh.  

Is there a way to find out who's a member of the "database_user"?  Example, I get "dc_admin", would like to find out who's a member of that role.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40450108
use the same query just change 'SQLAgentUserRole' to 'dc_admin'

dc admin and dc_operators are the data collector roles.  usually an SQL Server internal user will be the sole member of this role
0
 

Author Closing Comment

by:holemania
ID: 40450318
Thank you.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A short film showing how OnPage and Connectwise integration works.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now