Analysis Services: Removing Instance Level Admin Rights from Developers….

  • Article

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 Smile

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

image

 

image

 

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”

image

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.

<Restore xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <File>C:\Program Files\Microsoft SQL Server\MSAS10_50.OLAP\OLAP\Backup\Contoso Retail.abf</File>
  <DatabaseName>Contoso Retail</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
</Restore>

and it works completely well.

You can find required permissions for backup/restore operation in following link:

https://msdn.microsoft.com/en-us/library/ms174874(v=sql.105).aspx

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

clip_image002

This is screenshot from Trace, UserC is running

clip_image004

Now if usera access Contoso Retail database, this will appear on trace that userC is running on. Screenshot attached

clip_image006

 

if you have any questions, feel free to contact me.