Allowing user to backup only - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Allowing user to backup only

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-16 : 17:32:08

I have databases

TESTDB db owner testuer

Login testuser

When right clicking on logins i see dbo
When open this i see testuser

So i clicked on Role Members db_backupoperator was denied
had to do it on the owner schema and select db_backupoperator

Not sure why the role member is permitted.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-16 : 17:52:16

Did you have a question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-16 : 18:35:55

Question was i did not understand why i had to click db_backupoperator in the Schema vs the role window in gui?

In the role window i got add memeber failed for database role.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-16 : 22:16:28

Can't reproduce your problem on my servers.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-17 : 08:47:22

Hmmm.
Create the login and do not give it any mappings.
Then just click on the DB Properties, Files and owner is testuser

Then in DB Security Logins
DBO (Try here).

Im getting it every time

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-17 : 22:49:47

Don't get what you said.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-18 : 08:57:06

For testing.
I created new database and a new user adding no mappings to any database.

Then right click on new database and add the new user as OWNER

Under new database, security , logins click on DBO
(This was where i trying to just give the db_backupoperator permissions).

(Here i get
Schemas owned by this user
or
Database role members

I was under the impresssion i could click on Database Role members
and click db_backupoperator but the message returns
(Add member failed for database role, cannot use the special prinipal dbo)..

So i clicked on schema owned by this user and choose db_backupoperator

I did not understand why the Database role memeber was giving message.

(So i tested with new user via sql studio could backup - but now their application which is 3rd party is saying user does not have permissions to perform backup)...
(If i give it sa rights works )

Trying too prevent giving sa rights...(not sure what other rights to give this application).

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-19 : 17:44:09

You can't add that user to the db once mapped it to dbo.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-04-22 : 01:24:52

try to add this new user under default schema as "dbo" and under login "Any valid login"

and then assign this "db_backupoperator" role member to your new user

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-22 : 14:36:16

I got it working by creating the backups under sa then the user was able to backup to the same bak name.

Must be a new thing in SQL 2005.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-22 : 23:04:06

Not really.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources