ผู้ตอบมากที่สุด

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.

คำตอบ

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

ตอบทั้งหมด

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

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......