2

Hello guys I have a question

I have a group in that group a user shouldn't have syadmin permissions but he must be on that group.

He has his login map by the group and his windows login on sql server

Is it possible to deny him the server role syadmin but remain on the public role while still remain on the group?

Thanks in advance for the help.

Improve this question
1
  • You can't deny a specific role, you just don't put them in that group. Straight answer to your question, nope.
    – user507
    Apr 29, 2014 at 19:30

1 Answer 1

Reset to default
3

Removing a principal from sysadmin role does not remove the principal from public role. You cannot use deny on members of sysadmin role or the object owners. Using sp_dropsrvrolemember system stored procedure to remove the principal from sysadmin role should be your solution. If somebody does not belong to the party, better kick him out. Remember you need to be sysadmin yourself if you want to remove another principal from sysadmin role.

EXEC master..sp_dropsrvrolemember [user], 'sysadmin';
go
Improve this answer
2
  • 1
    You can also be a member of securityadmin and modify membership of sysadmin role.
    – user507
    Apr 29, 2014 at 20:04
  • You are right. The securityadmin role should be treated as equivalent to the sysadmin role (Security Note from BOL). Thanks for pointing out.
    – user353gre3
    Apr 29, 2014 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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