Sql-server – How to setup a Login which can run an automated Database Refresh without giving away too much priv

permissionssql-server-2012stored-procedures

I would like to re-ask and extend the question posed in this thread if I may:

SQL Server permissions to create, restore, delete only some databases

I am also attempting to setup an 'isolated' Login-A on a server I'll call Target (SQL Server 2012), with just enough priv to run a stored procedure (with a @dbname argument) which does the following:

  • runs a Backup for @dbname on a different linked server (Source)
  • Alters @dbname offline and Drops it on the Target server
  • then Restores new @dbname on Target

I played with "Grant Create Any Database To Login-A", which does appear to fence Login-A from neighboring databases. But when I tried to run Alter as part of some Create Database DDL, it failed, so this doesn't seem to help the stored procedure sequence.

How do we give enough priv to Login-A to be able to Alter and Drop without it being turned on beyond the current DB being created? I want my users to be able to refresh their databases by themselves (via the proc) while protecting all the other databases on the server which they don't own and shouldn't be able to touch (and without creating an audit violation).

Best Answer

You need to use impersonation.

Let's assume that loginB has elevated privileges. Your stored procedure will have an "Execute as" line:

execute as login = 'yourdomain\loginB';
backup database @dbname to disk.......
revert;

You must grant impersonation on loginB to loginA. Also, loginA must have execute permissions on the stored procedure.
MSDN reference for EXECUTE AS: https://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx