Undocumented T-SQL Commands

2017-06-14 0 Comments

I recently encountered code similar to this in production:

IF (NOT IS_MEMBER('db_accessadmin') = 1) AND
 (NOT IS_MEMBER('db_owner') = 1)
 DBCC AUDITEVENT (109, 1, 0, N'<loginName>', N'<dbUserName>', NULL, NULL, NULL, NULL, NULL)

I had never heard of DBCC AUDITEVENT, and from what little I've found online, it is an undocumented command. Paul Randal mentions in a forum comment that it's used for internal auditing. But he doesn't explain further. I did some quick testing and concluded the authorization to execute DBCC AUDITEVENT is membership in either [db_accessadmin] or [db_owner] (membership in the fixed server role [sysadmin] would also do the trick, natch.)

If not authorized, SQL generates this error:

Msg 2571, Level 14, State 2, Line XX
User '<userName>' does not have permission to run DBCC auditevent.

Looking back at the code, anyone that's not a member of [db_accessadmin] or [db_owner] is going to attempt to run a DBCC command they aren't authorized to run. Color me nonplussed. Maybe the developer knows something I don't. But since the command is undocumented, all I can do is guess.

I've seen some pretty strong opinions about undocumented commands. I'm generally against their use, although I do use at least one in production myself. If you understand the ramifications of using an undocumented command, it's preachy to say "don't do that". But for those of you who do, I'd plead with you to do one thing: leave some comments. That's good advice for any code, of course.