Let's say I have MS SQL Server 2012 Standard installed on Windows Server 2008 R2 Standard. It's a single SQL instance that runs under the windows domain account DOMAIN\sql_user. There's two databases, db1 and db2. There's two Windows domain accounts that are granted permission to login: DOMAIN\user1 and DOMAIN\user2. user1 has access to db1 and user2 has access to db2; each user cannot access the other's DB. user1 has permission to create DB backups and restore DB backups for db1, user2 has the same for db2. Let's say the backups are saved to D:\SQL\Backups[db1|db2]. How can I restrict what backup files these users can backup/restore to/from so they can only see their respective backup files? Ex, how can I restrict user1 to only restore files from D:\SQL\Backups\db1\ and not from D:\SQL\Backups\db2\, and vice versa for user2?
Thanks for any info!
Setting the correct NTFS permissions on the folders would do what you want. Has nothing to do with SQL server.
/Edit - looks like I'm wrong. If the access to the BKF is performed by the service account (which does surprise me), then I'm not sure there's a way to do what you want.
Using two instances of SQLServer you can do that. Each instance run at diferent SO user, each SO user will be granted with diferent permissions on disk.