MS SQL

Cute: I need to remove the db_backupoperator permissions from the db_owner role.

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2009

Posts: 3

Reputation: BratNZ is an unknown quantity at this point

Solved Threads: 0

BratNZ

Offline

Newbie Poster

Cute: I need to remove the db_backupoperator permissions from the db_owner role.

Oct 28th, 2009

The reason is that :

1. I wish to run multiple databases on a single server.
To do this securely each login must be granted db_owner on the database so we can use "deny view any database" to hide the other databases from view. (login can see all databases they are owner of however).

2. This creates a problem. We do not wish the users to be able to create backups even though they are db_owners.
It's here that I am having difficulties.

Any ideas?

•

Join Date: Nov 2009

Posts: 2

Reputation: forjo is an unknown quantity at this point

Solved Threads: 1

forjo

Offline

Newbie Poster

Nov 2nd, 2009

I assume you are running SQL Server 2005.
I far as i know it is not possible to change the permissions of a fixed database role (db_owner).
On database level try "DENY BACKUP DATABASE TO db-user" and "DENY BACKUP LOG TO db-user" where db-user is the database user (not the login!) who is member of the fixed database role "db_owner".

•

Join Date: Oct 2009

Posts: 3

Reputation: BratNZ is an unknown quantity at this point

Solved Threads: 0

BratNZ

Offline

Newbie Poster

Nov 2nd, 2009

•

Originally Posted by forjo

I assume you are running SQL Server 2005.
I far as i know it is not possible to change the permissions of a fixed database role (db_owner).
On database level try "DENY BACKUP DATABASE TO db-user" and "DENY BACKUP LOG TO db-user" where db-user is the database user (not the login!) who is member of the fixed database role "db_owner".

SQL2008 actually but those commands seemed to do the trick! Ta!