Member Avatar for 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?

  • 2 Contributors
  • 3 Replies
  • 271 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by forjo

Dani AI

Generated

Short answer: the quick fix that suggested (explicitly denying the backup permissions for the database user) will stop backups, and it worked for in this thread. That works because SQL Server supports GRANT/DENY at the database level and DENY takes precedence over GRANT. However, it is brittle: membership in the db_owner role (or being the database owner) grants CONTROL/ownership inside the database and lets that principal change or revoke permissions, so a db_owner can undo an explicit DENY. See the Microsoft docs on database roles and the permission model.
Database-level roles (db_owner/db_backupoperator) · Permissions: GRANT/DENY/REVOKE

A safer pattern is to avoid giving end users db_owner at all and create a narrowly-scoped, user-defined role that grants only the permissions the application or user actually needs (schema changes, DDL, data ops, or whatever). Example pattern (run inside the database you want to protect):

USE [YourDatabase];
CREATE ROLE app_owner;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO app_owner;
ALTER ROLE app_owner ADD MEMBER [YourDbUser];

Grant only the specific permissions required; this keeps backup/administration capabilities out of the role unless explicitly granted. Microsoft recommends granting the least privilege and using role-based grants instead of fixed roles for everyday use.
Database-level roles guidance

Troubleshooting tips: confirm the DENY was applied to the database user (not just a login), and confirm the account is not the database owner (owners bypass GRANT/DENY). To find explicit DENY entries in a database:

SELECT dp.state_desc, dp.permission_name, p.name
FROM sys.database_permissions dp
JOIN sys.database_principals p ON dp.grantee_principal_id = p.principal_id
WHERE dp.state_desc = 'DENY';

Also note third‑party backup tools that use SQL VDI often require sysadmin on the instance, so removing db_backupoperator may not block every possible backup method. For how CONTROL/REVOKE works and the VDI caveat see the REVOKE docs and vendor guidance.
REVOKE / CONTROL notes · VDI / backup agent requirement (vendor doc referencing Microsoft KB)

Recommended Answers

All 3 Replies

Member Avatar for 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".

Member Avatar for 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!

Member Avatar for forjo

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.