Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Jobs - Users that can create/modify/delete

Posted on 2014-11-18
4
Medium Priority
?
192 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 2000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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