2

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)

Improve this question
4
  • 1
    Aaron is correct in his answer. But a little trick i like to use when i can't figure this stuff out is in SSMS, if you make the amendments to the user/object you'd like to do programmatically in the GUI first, but rather than click OK, click "Script" from the top bar, it will open a new query window with the TSQL you need to do it next time, parameterize it and hey presto nice easy automation for the future.
    – Ollie
    Commented Jul 23, 2020 at 11:26
  • @Ollie generally that's a good tip, but in this case, the dialog if you go through the role instead of the login presents a remove button that acts immediately, so no way to use the script button there. Also, often, I'll come across things that SSMS still scripts out in the old/deprecated/not best practice way. So use caution.
    – Aaron Bertrand
    Commented Jul 23, 2020 at 14:10
  • @Ollie I just tried to use you suggestion (SQL Server2008). It works perfectly! Too bad I read you suggestion too late... Many thanks anyway!
    – Andrea Antonangeli
    Commented Jul 23, 2020 at 15:56
  • @AaronBertrand you are correct yes if you are trying to remove the User from the Role in the GUI you miss out on that option. However when removing the Role from the User in the GUI the script generated the ALTER SERVER ROLE statement (on SQL 2019) from your answer. I agree i have also noticed SSMS will sometimes generate scripts far from best practice unfortunately, however interesting to see how it does it sometimes, the above being a good example.
    – Ollie
    Commented Jul 29, 2020 at 22:20

2 Answers 2

Reset to default
4

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

Improve this answer
2

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.

Improve this answer
3
  • Thanks for your answer, but it seems that ALTER SERVER ROLE is usable from SQL Server 2012, not in SQL Server 2008, as stated here: dba.stackexchange.com/questions/119520/… So, my question remains...
    – Andrea Antonangeli
    Commented Jul 23, 2020 at 15:29
  • @AndreaAntonangeli Sorry, I missed that you were using SQL Server 2008 (which has been out of support for a long time). This may still be useful for other readers who are on supported versions.
    – Aaron Bertrand
    Commented Jul 23, 2020 at 15:50
  • Yes, your answer is useful and it helped me going deeper in this question to find the solution
    – Andrea Antonangeli
    Commented Jul 23, 2020 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.