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

[BratNZ]

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?

[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".

[BratNZ]

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!

[forjo]

That makes me happy, considering I didn't test it ...