none
how to setup a job to notify me when someone new is granted SA

    คำถาม

  • it'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.
    22 กุมภาพันธ์ 2555 19:23

คำตอบ

  • 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

    • เสนอเป็นคำตอบโดย Iric WenModerator 24 กุมภาพันธ์ 2555 6:50
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 2 มีนาคม 2555 3:11
    22 กุมภาพันธ์ 2555 20:41
  • 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

    • เสนอเป็นคำตอบโดย Iric WenModerator 24 กุมภาพันธ์ 2555 6:50
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 2 มีนาคม 2555 3:11
    22 กุมภาพันธ์ 2555 21:11
  • 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'
    ,@recipients='user@domain.org'
    ,@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

    • เสนอเป็นคำตอบโดย Iric WenModerator 24 กุมภาพันธ์ 2555 6:50
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 2 มีนาคม 2555 3:11
    23 กุมภาพันธ์ 2555 2:21

ตอบทั้งหมด

  • 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

    • เสนอเป็นคำตอบโดย Iric WenModerator 24 กุมภาพันธ์ 2555 6:50
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 2 มีนาคม 2555 3:11
    22 กุมภาพันธ์ 2555 20:41
  • awesome. thanks.

    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.

    22 กุมภาพันธ์ 2555 20:51
  • 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

    • เสนอเป็นคำตอบโดย Iric WenModerator 24 กุมภาพันธ์ 2555 6:50
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 2 มีนาคม 2555 3:11
    22 กุมภาพันธ์ 2555 21:11
  • 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......

    22 กุมภาพันธ์ 2555 21:13
  • 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'
    ,@recipients='user@domain.org'
    ,@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

    • เสนอเป็นคำตอบโดย Iric WenModerator 24 กุมภาพันธ์ 2555 6:50
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 2 มีนาคม 2555 3:11
    23 กุมภาพันธ์ 2555 2:21
  • beautiful. thanks
    23 กุมภาพันธ์ 2555 16:37