November 16, 2016 at 10:24 am
Hello. I have a user on a SQL Server that is used for executing SSRS queries. This user was made a sysadmin before I started at this job. I want to remove the sysadmin permission, but not break any reports. I do not know what DDL/DML the user is actually performing.
With that said, I want to run an audit, which I have done before, but since the auditing is so fine grained, I can't wrap my head around what all I should audit to catch the user actions on all databases.
Can anyone shed some light?
Thanks in advance!
November 16, 2016 at 11:00 am
There are some good articles on this site related to event Auditing, including how to filter for specific types of operations and users. http://www.sqlservercentral.com/articles/Stairway+Series/130498/
You can also fall back to using a SQL Profiler trace, which is a lot easier to setup. You'll want to set a filter on this specific account.
For the fix, the first step is to identify databases that this account touches, drop the account from SYSADMIN, and then add them as DBO at the database level.
For DDL, if what they are doing is creating scratch tables, then advise them to use temporary tables instead. Or you can grant them ALTER permission on a specific schema within the database that belongs only to this account.
GRANT Schema Permissions
https://msdn.microsoft.com/en-us/library/ms187940.aspx
Once done, then you can work on incrementally dropping them from the DBO role(s).
It's not typical for a reporting process to perform DML (much less DDL) operations. In this situation I'd be more concerned about the potential for the user to break the server instance itself, rather than you breaking a report. In my opinion, protecting the data and infrastructure is more important than insuring 100% availability, at least for back office type reporting applications. You may agree with me on this too, but let's keep that to ourselves :-).
If you do happen to restrict them in such a way as to break a report, then you can quickly mitigate this by re-adding them to SYSADMIN role temporarily until you figure out what additional permissions they need.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 16, 2016 at 11:27 am
Thanks for the reply and the article. Good stuff.
I agree with your points. Additionally, I am going through the list on this page to figure out what I need: https://msdn.microsoft.com/en-us/library/cc280663(v=sql.110).aspx
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply