Minimum permission to take database backup- are not used

Question

Hi,

I am using Microsoft.SqlServer.Smo to take database backup successfully......

I am using following code

==========

ServerConnection conn = new ServerConnection(remoteSQLServer, username, password);
 Server server = new Server(conn);
// Reference the database.
                Database remoteDatabase = server.Databases[remoteServerDatabase];
                string strBackupDir = server.Settings.BackupDirectory;
                // Define a Backup object variable. 
                Backup backup = new Backup();

//set other required properties of backup object

 backup.SqlBackup(server);

===========

But the least privileges required to take backup are

Server level - PUBLIC ROLE

Database level - DB_BACKUPOPERATOR .

Consider there are four users User1, User2, User3 & User4

These users are added on SQL Server with permissions greater than public role

but i am taking database backup of a database say MyDB which is having only User1 added in it(under "Security-->Users" of MyDB)...

Now i am taking backup of MyDB using above code and provided user name & password of User4(which is not added in MyDB) still i am able to take backup......

I thought since User4 is not added in MyDB i should not be able to take backup???since it do not even have minimum permission???

---

Naren

Answer 1

Hi,

what is the result of this query running by user4?

USE [MyDB]
SELECT * FROM fn_my_permissions(null, 'SERVER');
GO
SELECT * FROM fn_my_permissions(null, 'DATABASE');
GO

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 2

Hi Janos,

Please find attached images after executing this query.

I think i found the reason.

The User4 has the Server Role as "public" and "sysadmin" under the server security.

so there is no need for User4 to be added in any database......he can access any database eventhough he is not added in it....though we can add this user under database....

let me know if i am wrong.....and if it is the reason then i am sorry for wasting your time.

---

Naren

Answer 3

Hi Naren,

You are right. Since user4 has sysadmin right, it has all permission to perform backup.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me