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

  • Hi

    One option is to create a SQL Server Agent WMI alert using the DDL ADD_SERVER_ROLE_MEMBER which notifies an operator when detected e.g. for a default instance the Namespace in the WMI alert would be \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER and the Query would be SELECT * FROM ADD_SERVER_ROLE_MEMBER
    WHERE RoleName = 'sysadmin'

    You can learn more about WMI events from here http://msdn.microsoft.com/en-nz/library/ms186449.aspx

    Sunday, July 28, 2013 3:58 AM
  • Hi,

    Try this link - http://dba.stackexchange.com/questions/6136/how-can-i-create-a-notification-method-when-someone-adds-a-user-login-to-have-s


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Sunday, July 28, 2013 7:04 AM
  • Hi Kevin,

    Thanks for link it worked fine for 2008 databases.

    Is there any way out for sql 2000 databases?

    regards,


    -

    Sunday, July 28, 2013 10:32 AM
  • 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
  • Thanks.. it worked...

    Step 1 - 

    CREATE table Sysadmin_Members (ServerRole sysname,MemberName sysname,MemberSID varbinary(85));
    insert Sysadmin_Members exec sp_helpsrvrolemember 'sysadmin';

    Step2- 

    BEGIN


    DECLARE @Sysadmin_Memebers AS TABLE
    (
    ServerName NVARCHAR(100)
    ,   Name NVARCHAR(100)
    , Loginname NVARCHAR(100)
    ,   [sid] VARBINARY(85)
    , Createdate DATETIME
    , Updatedate DATETIME
    ,   Denylogin INT
    ,   Sysadmin INT

    )

    INSERT @Sysadmin_Memebers
    SELECT
    @@SERVERNAME AS ServerName, 
    [name], 
    loginname, 
    [sid],
    createdate,
    updatedate,
    denylogin, 
    sysadmin
    FROM master.dbo.syslogins WITH (NOLOCK)
    WHERE sysadmin <> 0 ;


    SELECT *
    INTO #Temp_Admin
    FROM @Sysadmin_Memebers p
    WHERE [sid] NOT IN (SELECT MemberSID FROM dbo.Sysadmin_Members)

         IF(@@ROWCOUNT > 0 )
      BEGIN

    DECLARE @tableHTML  NVARCHAR(MAX) ;
    DECLARE @Subject VARCHAR(50);
    SET @Subject = 'SQL Administrator Accounts on ' + @@SERVERNAME
    SET @tableHTML =
     N'<html>
    <head>
    <style>
    *{font-family: calibri, ariel, helvatica, sans-serif;}
    table{border-collapse: collapse;}
    td,th{border:1px solid #000; padding:2px;}
    th{border-bottom: 3px solid #000;}
    tr:nth-child(even){background-color:#fff;}
    tr:nth-child(odd){background-color:#eee;}
    </style>
    </head>
    <body> '+
      N'<H1>SQL Administrator Accounts</H1>' +
        N'<table cellspacing="0">' +
        N'<tr><th>ServerName</th><th>Name</th>' +
        N'<th>LoginName</th><th>CreateDate</th><th>UpdateDate</th>' +
        N'<th>DenyLogin</th><th>sysadmin</th></tr>'+
        CAST ( ( 
    SELECT 
    td = ServerName,'', 
    td = [name],'', 
    td = loginname,'', 
    td = CONVERT(VARCHAR,createdate,112),'',
    td = CONVERT(VARCHAR,updatedate,112),'',
    td = denylogin,'', 
    td = sysadmin,''
    FROM #Temp_Admin
          FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table>
    </body>
    </html>' ;

    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = '',
    @recipients='',
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';
    -- PRINT @SereverName ;
    -- PRINT  @Name;
    ----SET @i =@i+1;
    END;

    DROP TABLE #Temp_Admin;


    END;
    Tuesday, March 22, 2016 2:02 PM