Click here to Skip to main content
15,559,491 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a database named for example "StoreDB".
I want to give only "sa" user permission to make backup and restore a datbase, just when the login password for "sa" is the same login password that I gave "sa" permission to make backup and restore.

To be clear:
if someone get my database (.mdf or .bak) he can not restore it unless if he login with "sa" user and the password is what I set it.

Q: what I should do to do that?
any video link describe that will be good.

thanks in advance.
and sorry for the writing, My English language is not good.

What I have tried:

I googled the problem but I did not find what I want.
Updated 10-Nov-22 4:15am

1 solution

You're trying to solve the wrong problem. If you want to protect your backup, you don't do it by comparing the user's password; you do it by encrypting the backup.

Backup Encryption - SQL Server | Microsoft Learn[^]
Share this answer
Ghost Ghost 10-Nov-22 9:27am    
Is it work with .mdf file also?
Richard Deeming 10-Nov-22 9:32am    
A "loose" MDF file shouldn't exist; when it's in use, SQL Server keeps it locked, so nobody can copy it.

So either you're trying to stop an attacker who can stop your running SQL Server instance to copy your database files - in which case, you've already lost - or you're using SQL Express "user instances", which have always been a bad idea.

If you're really worried about your databases being stolen, then you should enable Transparent data encryption (TDE)[^].
Ghost Ghost 10-Nov-22 10:49am    
thank you very much. that was helpful.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900