I would like to re-ask and extend the question posed in this thread if I may:
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).