Solved

SQL Jobs - Users that can create/modify/delete

Posted on 2014-11-18
4
133 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

18 Experts available now in Live!

Get 1:1 Help Now