none
How to get alerts when new logins are added to sa role?

    Question

  • Hi ,

    Can some one help me on the below requirement

    I need to get notified with an alert when new login has been added to SA role.

    I have some ideas to use sp_sendmail . Please assist me

    regards,
    team


    -

    Sunday, July 28, 2013 1:24 AM

Answers

  • Hi

    One method for 2000 is to

    1. Create a permanent table in a database with the current members of the sysadmin role e.g. using  

    exec sp_helpsrvrolemember 'sysadmin'

    2. Schedule a SQL Server agent job which executes regularly and
    a) creates a temporary table which contains the current members of the role
    b) checks against the permanent table if the members are different
    c) sends an email if any changes are detected
    d) update the permanent table with the current members if there were changes

    I don't have a SQL 2000 instance available, but some code to populate a temp table with the current members is shown below:

    create table #t1 (ServerRole sysname,MemberName sysname,MemberSID varbinary(85));
    insert #t1 exec sp_helpsrvrolemember 'sysadmin';
    select * from #t1;
    drop table #t1;

    Sunday, July 28, 2013 6:54 PM
  • SELECT roles.[name] AS 'Role', prin.[name] AS 'Member'
    FROM sys.server_role_members mem
      JOIN sys.server_principals roles
        ON mem.role_principal_id = roles.principal_id
      JOIN sys.server_principals prin
        ON mem.member_principal_id = prin.principal_id
    ORDER BY roles.[name], prin.[name];

    Filter on sysadmin role and create a job that checks pre-difened list.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 30, 2013 9:54 AM

All replies