x

Capture setuser events with SQLServer Auditing

I am working on creating an audit for a database. I have incuded impersonation in my audit spec

ALTER DATABASE AUDIT SPECIFICATION [dbName] ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP)

The captures execute as within a stored procedure. However if I use the SETUSER command from a query window and select from a table nothing is logged in the audit.

How do I capture the setuser command with SQL Server audit?

As ever I bow before your collective wisdom.
more ▼

asked Jun 01, 2012 at 02:43 PM in Default

half fast dba gravatar image

half fast dba
152 8 9 10

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Since SETUSER is a deprecated feature, it is not captured through Audit Specification, although the BOL said so. It is not even possible through a trace (EventClassIds 132 & 133). But I would be less concerned as compared to EXECUTE AS, as SETUSER is only allowed to sysadmins and the database owner.

Now, for sysadmins, you cannot do much about, as they can always get around any audit specification.

For database owner, the strange thing is, although BOL states that the user must be a member of db_owner fixed database role, only the actual owner

SELECT SUSER_SNAME(OWNER_SID) 
FROM sys.databases 
WHERE [name] = 'DBNAME'

can use the SETUSER statement successfully (I am talking about the recent builds and If someone can make it work as stated in BOL, then please correct me by posting an example. Would be very much appreciated). Hence, you are left with only one user.

Now the solution part; I guess one of the few options would be the DDL triggers. You can LOG / ROLLBACK the statements by use of the ORIGINAL_LOGIN() function (Which will identify the actual user). A generic database trigger example is as follows. You can modify it according to your need

CREATE TRIGGER LogAllDDL ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()

INSERT  Audit.AuditDDLOperations
        (
         OriginalLoginName
        ,LoginName
        ,UserName
        ,PostTime
        ,EventType
        ,DDLOp
        )
VALUES  (
         ORIGINAL_LOGIN()
        ,SYSTEM_USER
        ,CURRENT_USER
        ,GETDATE()
        ,@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
        ,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
        )
RETURN ;
GO
Hope it helps.
more ▼

answered Jun 04, 2012 at 02:11 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

Are you using an account that is dbo?

From BOL :

The scope of impersonation in the SETUSER statement is implicit. If the statement is called by a member of sysadmin, server-level impersonation is used. If the statement is called by an account that is dbo, database-level impersonation is used.

So maybe you need SERVER_PRINCIPAL_IMPERSONATION_GROUP rather than DATABASE_PRINCIPAL_IMPERSONATION_GROUP

Also you shouldn't have an issue if you stick with EXECUTE AS

>SETUSER is included for backward compatibility only. SETUSER may not be supported in a future release of SQL Server. We recommend that you use EXECUTE AS instead.
more ▼

answered Jun 01, 2012 at 02:48 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

I have captured server level impersonation events as well. Still SETUSER slides by the audit.

Also the audit is designed to catch naughty people and unfortunately they do not play by the rules. So I have to trap the SETUSER event somehow.
Jun 01, 2012 at 03:54 PM half fast dba
If you use EXECUTE AS, the new command, does it capture that event?
Jun 01, 2012 at 05:42 PM Shawn_Melton
Execute as is captured. Capturing the setuser command is the tricky part. You have to remember that other people do not have to play by your rules when it comes to accessing data!
Jun 06, 2012 at 09:16 AM half fast dba
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x43

asked: Jun 01, 2012 at 02:43 PM

Seen: 853 times

Last Updated: Jun 06, 2012 at 09:19 AM