December 16, 2016 at 10:31 am
Hello,
we re in the process of locking down our UAT/TEST servers, and we locking it down hard.
After I take a way privs.... how can I audit what login/users are running queries they shouldn't?
In other words... how I can tell who is receiving errors short of asking them...
"The SELECT permission was denied on the object "
I just need to do this for a short periord. Would a trace be the best way of doing this?
Or is there a flag I can enable to dump this into the errorlog?
Thanks
December 16, 2016 at 2:06 pm
You can add permission denied to your SQL Server logs by using:
EXEC dbo.sp_altermessage 229, 'WITH_LOG', 'true';
I have, however, noticed this doesn't give you the user name. However, if you have extended events set up, you should then be able to find who ran the query.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 16, 2016 at 2:21 pm
You'll want to setup an extended event trace to audit any permission denied related errors resulting attempts by applications to execute direct SQL statements. However, if we're just talking about attempts at ad-hoc querying, then personally I'd just let the users bring it up, if they care to. The error message they receive is just a reminder to them that they're doing something they shouldn't.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply