Sql-server – Drop permission from a dbowner

permissionssql server

How can we remove the drop permission from a user or a role like dbowner? Want to restrict the dbowner from dropping the database.

Best Answer

Even if you meant Sql Server and just mis-spelled db_owner, the answer holds true for any database: use the principle of least priviledge. That is, if an user shouldn't be able to drop the database, do not assign such privileges in the first hand at all.

Messing with built-in security group and role settings is bad practice, unless you are very sure what you are up to. Create new roles with appropriate settings instead. This way, you or any other DBA doesn't have to double-check if default permissions are really on default settings.

That being said, you might be able to prevent drops with some creative triggers. As how to do that, depends on your RDBMS.

Related Solutions

Sql-server – What permission is needed to set Database read-only

Form Query Analyzer or Management Studio, you can issue the following command:

ALTER DATABASE database-name SET READ_ONLY

As mentioned here, you need the alter database rights and you must set the database to single mode.

Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive access to the database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

Sql-server – Identify why a user can drop a database

The query you've got there will list only permissions for the database against which you run it. One way to get permission to drop a database is ALTER ANY DATABASE, which is a server-level permission. To check those, try this query:

SELECT 
  [srvprin].[name] [server_principal],
  [srvprin].[type_desc] [principal_type],
  [srvperm].[permission_name],
  [srvperm].[state_desc] 
FROM [sys].[server_permissions] srvperm
  INNER JOIN [sys].[server_principals] srvprin
    ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];

In other words, the user might be getting the permission to drop databases at the server login level rather than the database user level.

Best Answer

Related Solutions

Sql-server – What permission is needed to set Database read-only

Sql-server – Identify why a user can drop a database

Related Question