Problem with a backup of an DB (db_backupoperator)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with a backup of an DB (db_backupoperator)

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-14 : 15:49:21

Hi!
I have created a login and a user named backup_user. I am trying to give it access to the role db_backupoperator, which works (No error messages and it is also visible under 'Logins' -> 'Properties' -> 'User Mapping' (The DB is checked and also 'db_backupoperator' and 'public'). None of the 'Server Roles' is checked.

USE Projekt_IV138V_8611171490
-- "SQL Authentication"
CREATE LOGIN backup_user WITH PASSWORD = 'B@c4up_Us3r'


CREATE USER backup_user FOR LOGIN backup_user

-- Ta bort "alla" rättigheter först!
DENY ALL TO backup_user
-- Ge användaren rättigheter till db_backupoperator-rollen:
EXECUTE sp_addrolemember 'db_backupoperator', 'backup_user'

When I am login in with this user, without any problem, I have tried to write this:

BACKUP DATABASE Projekt_IV138V_8611171490

but recieved this error message:

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'Projekt_IV138V_8611171490'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I also tried to do a backup from the GUI (Right click the DB -> 'Tasks' -> 'Back Up' -> 'Ok') but recieves an 'Permission error' popup there also.

According to BOL:
"db_backupoperator - Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT"
http://msdn.microsoft.com/en-us/library/ms189612(SQL.90).aspx

I also found some interesting information on this page:
http://www.mssqlcity.com/Articles/Adm/SQL70Roles.htm

but unfortunately it didn't help me.

It seems that I have set it up correctly with the db_backupoperator role but I must have missed something ('DENY ALL' thingy at the beginning?).

Any tip?

Thanks in advance!

Best Regards,
Tomas

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-14 : 15:56:51

Does that user have (db_backupoperator) for the database you are trying to backup? Also User should have access to path.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-14 : 16:04:01

quote:
Originally posted by sodeep

Does that user have (db_backupoperator) for the database you are trying to backup? Also User should have access to path.

Hello!
The user had db_backupoperator access under 'Role Members' but not under 'Owned Schemas'.

I tried to set db_backupoperator access under 'Owned Schemas' also but with no success.

EDIT: That user access is for the database: Projekt_IV138V_8611171490

Best Regards,
Tomas

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-14 : 16:16:29

Expand the Security-logins- scroll through the login and in user mapping -check db_backupoperator for that login with proper database.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-15 : 09:00:10

quote:
Originally posted by sodeep

Expand the Security-logins- scroll through the login and in user mapping -check db_backupoperator for that login with proper database.

Hi!
Check this picture, it seems correct:
http://img73.imageshack.us/img73/6246/dbbackupoperatornj5.jpg

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 10:27:45

Yes it is. So did that work?

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-15 : 11:04:21

quote:
Originally posted by sodeep

Yes it is. So did that work?

I assumed that everyone though what I was thinking =)

But no, it didn't work.

Best Regards.
Tomas

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 11:06:31

what is the error?

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-15 : 11:10:15

quote:
Originally posted by sodeep

what is the error?

From the GUI:
http://img502.imageshack.us/my.php?image=deniedsq1.jpg

From T-SQL:

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'Projekt_IV138V_8611171490'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 11:19:45

It is working for me.

Post the result of

sp_helplogins 'backup_user'

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-15 : 11:39:36

quote:
Originally posted by sodeep

It is working for me.

Post the result of

sp_helplogins 'backup_user'

LoginName              SID                                                                                                                                                                          DefDBName    DefLangName          AUser ARemote
---------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------- ----- -------
backup_user            0xA9270B51663BAA41B5A163DC673BC4B3                                                                                                                                           master       us_english           yes   no     

(1 row(s) affected)

LoginName              DBName                                             UserName                           UserOrAlias
---------------------- -------------------------------------------------- ---------------------------------- -----------
backup_user            Projekt_IV138V_8611171490                          backup_user                        User    
backup_user            Projekt_IV138V_8611171490                          db_backupoperator                  MemberOf

(2 row(s) affected)

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-15 : 17:11:16

Can it be something with that "my" user uses "Windows Auth." and I enabled "SQL Auth." to be able to create this particular user?

Best Regards,
Tomas

dsithoo
Starting Member

2 Posts

Posted - 2010-03-05 : 15:56:28

The answer lies in your very 1st post. The fact that you DENY ALL to backup_user means the principal does not have access to db_backupoperator necessary permissions which would be normally granted, or more specifically to the BACKUP DATABASE and BACKUP LOG permissions. Use REVOKE to remove DENY permission.

See the following from BOL:
http://msdn.microsoft.com/en-us/library/ms188338(SQL.90).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources