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.
asked Jun 01 '12 at 02:43 PM in Default
half fast dba
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
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
Hope it helps.
answered Jun 04 '12 at 02:11 PM
Are you using an account that is dbo?
From BOL :
So maybe you need
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.