How to remove dbcreator server role?

Question

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)

Andrea Antonangeli · Answer

OK, found how to do it in SQL Server 2008:
EXEC master..sp_dropsrvrolemember  
   @loginame = N'YourLogin', 
   @rolename = N'dbcreator';

Thanks again for the suggestions: when this DB will be migrated now I know that probably I'll have to modify the script to use ALTER SERVER ROLE

Aaron Bertrand · 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.

How to remove dbcreator server role?

2 Answers

Add your own answers!

Ask a Question