Post reply

Deny Security Permissions for a SysAdmin

  • February 4, 2015 at 2:01 pm

    #314917

    Is there a way to deny Security Permissions to a login that has sysadmin? Unfortunately I have to leave the user as sysadmin. I trying denying alter any login and control server but that didn't work.

  • February 4, 2015 at 2:36 pm

    #1775475

    nope, it is not possible to deny a sysadmin access to anything.

    permissions checks are shortcutted if you are sysadmin, and all deny/revoke permissions you might create are ignored.

    the only solution is to take away sysadmin privileges, which you said you can't do.

    only then can you restrict a users permissions.

    are you sure you can't just make a new but reasonablypowerful role, take away sysadmin and dump them into that role?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 4, 2015 at 2:49 pm

    #1775481

    I'd like to. The issue is that the user needs to be able to modify all sql agent jobs. I've tried to do this with msdb permissions to SQLAgent but that only allows the user to create new jobs and modify ones that are owned by them.

  • February 4, 2015 at 9:06 pm

    #1775555

    Why don't you try giving them the db_owner role in MSDB and restricting the user's permissions elsewhere? This would limit what they can do outside of MSDB. At least the user would not have total control over the entire SQL instance.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply