1

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).

  • Perhaps a related question will help provide some context: Does the scope for 'Alter Any Database' operate similar to 'Create Any Database', as in, only those databases created by Login-A can be altered, and not the other databases on the server? – Jake May 27 '15 at 1:48
0

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.