SQL Server – How to Remove dbcreator Server Role

permissionssql serversql-server-2008sql-server-2008-r2

I was able to add the dbcreator server role to a user of a SQL Server DB, thanks to this nice question/answer: create-database-permission

How can I REMOVE that permission? Is there a stored procedure like sp_addsrvrolemember that works on the contrary and that I can use to drop that permission for the same user for which I granted it?

I tried using sp_droprolemember, but i got a "dbcreator is not a role" error message.

I need to do it programmatically.

Many thanks in advance!

(Sorry if a similar question was already asked, but I was not able to find it)

Best Answer

You can only add logins to server roles, not database users. To remove a login, you use ALTER SERVER ROLE ... DROP MEMBER:

ALTER SERVER ROLE dbcreator DROP MEMBER login_name;

Please stop using the stored procedures; they're all deprecated. For example, from the sp_addsrvrolemember documentation:

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SERVER ROLE instead.

Related Solutions

Sql-server – SQL Server – DBO without Backup

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema). This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.

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 – SQL Server – DBO without Backup

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

Related Question