SQLServerWiki

“The Only Thing That Is Constant Is Change”

Auditing ADD_SERVER_ROLE_MEMBER

Posted by database-wiki on October 4, 2011

–creating a database audit. I am creating a new database but you can use the existing database as well.

USE Master;

GO

CREATE DATABASE Audit;

–Enabling service broker to the database

USE audit;

GO

ALTER DATABASE audit SET ENABLE_BROKER;

–creating a table to hold the records

USE audit;

GO

CREATE TABLE audit_events

(

Command NVARCHAR(1000),

PostTime NVARCHAR(24),

LoginName NVARCHAR(100)

)

–Creating a queue

CREATE QUEUE Audit_Queues WITH STATUS = ON

–creating a service

CREATE SERVICE Audit_Services

ON QUEUE Audit_Queues

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

–creating a route

CREATE ROUTE ACTION_Notify

WITH SERVICE_NAME = ‘Audit_Services’,

ADDRESS = ‘LOCAL’

–creating an event

CREATE EVENT NOTIFICATION Audit_add_role_Events

ON SERVER

FOR ADD_SERVER_ROLE_MEMBER

TO SERVICE ‘Audit_Services’,’current database’

–stored procedure to get the details from the queue and insert into a table.

create  PROC SP_Activate

AS

BEGIN

–Declare variables to pull data from queue

DECLARE @messageTypeName NVARCHAR(256), @messageBody XML ;

–Pull data from queue

RECEIVE TOP(1)

@messageTypeName = message_type_name,

@messageBody = CAST(message_body AS xml)

FROM Audit_Queues;

IF @@ROWCOUNT = 0 –Nothing in queue

RETURN –Exit

–Declare variables to INSERT data into LogEvents table

DECLARE @cmd NVARCHAR(1000), @posttime NVARCHAR(24), @spid
NVARCHAR(6)

DECLARE @loginname NVARCHAR(100)

–Populate variables

/*SET @cmd = CONVERT(NVARCHAR(100),

@messagebody.query(‘data(//TSQLCommand//CommandText)’))*/

SET @cmd= CAST(@messageBody AS NVARCHAR(1000))

SET @posttime =
CONVERT(NVARCHAR(24),@messagebody.query(‘data(//PostTime)’))

SET @spid =
CONVERT(NVARCHAR(6),@messagebody.query(‘data(//SPID)’))

SET @loginname = SYSTEM_USER

–Insert data into LogEvents table

INSERT INTO audit_events(Command,PostTime,LoginName)

VALUES(@cmd, @posttime, @loginname)

END

–enabling an activation stored procedure to be execute whenever a new message appears in the queue.

alter QUEUE Audit_Queues

WITH STATUS = ON,

RETENTION = ON,  –set to on will keep the message in the queue. You can make if off so that the
message is deleted from the queue after a record is created in the table

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = SP_Activate,

MAX_QUEUE_READERS = 5,

EXECUTE AS OWNER

);

–Testing

sp_addsrvrolemember ‘q’, ‘SysAdmin’

–Querying the table

SELECT * FROM audit_events

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: