How to give the user sa permission to backup or restore only if the login password is correct

Question

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.

Posted 10-Nov-22 3:12am

Ghost Ghost

Updated 10-Nov-22 3:15am

v2

Add a Solution

1 solution

Add a Solution

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

Richard Deeming · Accepted Answer · 2022-11-10T03:15:00

Solution 1

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[^]

Posted 10-Nov-22 3:15am

Richard Deeming

Comments

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.

How to give the user sa permission to backup or restore only if the login password is correct

1 solution

Solution 1

Add your solution here

Preview 0

How to give the user sa permission to backup or restore only if the login password is correct

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us