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.

  • This will work even though the restore operations are performed through SQL server? I want to make sure that running RESTORE as DOMAIN\user1 will restrict access to the appropriate folders. – Dan May 1 '13 at 18:58
  • 1
    I will repeat myself. The SQL operations have nothing to do with this. If you have set permissions on the DB1 folder to disallow User2 to read it, then User2 will not be able to access the files in DB1. – mfinni May 1 '13 at 19:18
  • Created a backup of DB 'test' using user DOMAIN\user1. Applied NTFS deny for full control for DOMAIN\user2 to said DB backup folder. Ran a cmd prompt as DOMAIN\user2. user2 is unable to access DB backup folder. From that same cmd prompt, osql command was executed to restore the DB from that path and it was successful. – Dan May 2 '13 at 18:26
  • I find that odd. I don't have a SQL server in front of me to play with. You could run ProcMon to see what user account is accessing the BKFs during the restore. Was OSQL run as the User2 login, or a SQL login? The User2 login isn't a holder of SQL sysadmin role, is it? – mfinni May 2 '13 at 18:43
  • And you're certain that none of the groups that User2 is a member of have read permissions on the file? Try that - even if User2 can't read the folder, it can be configured that User2 can read a file in the folder. It's possible that NTFS permissions inheritance in your environment isn't what I'm assuming it should be. – mfinni May 2 '13 at 18:50

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.

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.