One of popular questions in these days: Is it possible to remove admin rights from developers but they can still run backup/restore operations as well as profiler trace? I know, from developer’s perspective, this is like removing their freedom for things they developed but on the other hand, from Management perspective, admin rights should only belong to Database/BI Operational team. It is a very long running debate
Short Answer: YES
Here is my scenario and steps to limit permission:
In this scenario, we have two users : UserA and UserC
UserA: Instance Admin (represents DBA Permission)
UserC: Database Level Admin (Represents Developer who has admin rights for specific database)
Two databases: Adventure Works and Contoso Database
UserC hasFull Control over Contoso Database
Now, userC can run Backup/Restore commands againts Contoso Database.However there is one issue, We have identified. There is no problem on backup command through GUI however if you try to restore database through GUI, userC gets following error message “The ‘DOMAIN\username’ does not have permission to call the Discover method”
I suspect that this might be potential defect on GUI in SSMS by sending extra discover commands to Analysis Services. However, you can still restore database by using XMLA script.
<File>C:\Program Files\Microsoft SQL Server\MSAS10_50.OLAP\OLAP\Backup\Contoso Retail.abf</File>
and it works completely well.
You can find required permissions for backup/restore operation in following link:
Another feature your developers may want to use is Profiler Trace. Developers can run profiler trace but they can only see profiler trace events againts databases,they have admin rights on.
if you give full permission to user for specific database, user can run profiler trace and see all events related with that database only. For instance, UserC had admin rights for Contoso Database. UserC can run profiler trace against AS instance but userC can only see profiler trace events for Contoso Database. If UserA logs on to Contoso, this will appear on UserC trace. But if User logs on to Another Database Let’s call it AdventureWorks, in this case, This session doesn’t appear in UserC trace.
This is screenshot UserA is accessing Adventure Works
This is screenshot from Trace, UserC is running
Now if usera access Contoso Retail database, this will appear on trace that userC is running on. Screenshot attached
if you have any questions, feel free to contact me.