miércoles, 22 de febrero de 2012 19:23it'd be nice to have a job that emails me ONLY when someone new is granted sa. although they know they shouldn't, it seems we have folks on our systems who have the ability to grant sa. if i had some sort of TSQL command that can query and return just a list of sa users, i can put it into a maintenance plan for the acceptable scheduling and have it email the results. The sticking part for me is figuring out what to query, how to have the query results display just the login, and when the results aren't in the expected collection of users, email me. if this was PL/SQL i think i could do it, but being new to SQL Server and TSQL, i have no idea where to begin.
Todas las respuestas
miércoles, 22 de febrero de 2012 20:41
You could possibly use the below to return members of the sysadmin role, as they effectively have sa permissions
SELECT q1.name FROM master.sys.server_principals q1, master.sys.server_role_members q2 WHERE 1=1 AND q1.principal_id=q2.member_principal_id AND q2.role_principal_id = (SELECT q3.principal_id FROM master.sys.server_principals q3 WHERE q3.name='sysadmin') ORDER BY q1.name
miércoles, 22 de febrero de 2012 20:51
i can add an AND statement... something like:
AND q1.name not in ('sa', 'Administrator')
to parse out the known players. if someone new is added to the list, they'd show like a sore thumb.
BUT how do i invoke the sp_send_dbmail procedure when someone new pops into the list?
I know how to get sp_send_dbmail to email periodically the list but i'd rather have it email only when someone new is on the list.
miércoles, 22 de febrero de 2012 21:11
You would likely require a solution as follows
- create a table in a database (possibly a dedicated DBA administration database) that holds the current sysadmin members (table 1)
- schedule the a job to populate a second table from the query above (table 2)
- compare both tables and only send you an email if there is a difference (possibly a second job step which fires if a difference is found)
- replace the (table 1) database with the data from (table 2)
you could also look at adding a datetime column for more smarts which gives a sysadmin membership history
miércoles, 22 de febrero de 2012 21:13
essentially i need to figure out how to get sp_send_dbmail to parse the results and send an email when an IF clause is satisfied. I thought about getting my email filter to parse the results and if over a certain word count filter differently, but my email program isn't that sopisticated......
jueves, 23 de febrero de 2012 2:21
A quick example of an if cause for this scenario would be
IF (SELECT COUNT(*) FROM dba_database.dbo.table2 q1 WHERE 1=1 AND q1.name NOT IN (SELECT q2.name FROM dba_database.dbo.table1 q2)) >= 1 BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'test' ,@email@example.com' ,@subject='Principal added to sysadmin role' ,@query='SELECT ''The principal '' + q1.name + '' has been addeded to the sysadmin role.'' FROM dba_database.dbo.table2 q1 WHERE 1=1 AND q1.name NOT IN (SELECT q2.name FROM dba_database.dbo.table1 q2)' END
jueves, 23 de febrero de 2012 16:37beautiful. thanks