Revoke or Deny privilege RRS feed

  • Question


    In SQL 2005, I have user group is called IT group, they have only db_datareader and db_datawriter but they can create new user login and modifying existing logins. How I can deny or revoke privileges for that group not to alter the login users or create new users? I tried database level property and server level property to deny alter user and alter roles. But nothing is working still they can alter user logins.  please help me.

    Note: I have a SysAdmin Role.

    Success is where preparation and opportunity meet.
    Thursday, February 12, 2009 9:50 PM

All replies

  • Hi

    In order to prevent the user creation and altering of user properties, the role should be denied the "ALTER ANY USER" Permission.

    For example:

    In order to prevent the "myRole"  application role to create the users

    ALTER ANY USER TO [myRole]


    Friday, February 13, 2009 8:24 AM
  • Hi Sreekar,

                Thanks for your reply. Actually IT users having SQL 2005 Client and they changing or altering the user profile in SQL Client. Your advise helped me some extent. I have created in "myRole" in Database Roles and created new user then I applied that user in that role. Things is once I assigned that role, user(IT group) can give new role but not alter that role.

    Actually I want nobody can give new roles and they  can't alter exiting roles. any help please.

    I have also tried like this 

    DENY ALTER ANY Role TO [myrole]

    Success is where preparation and opportunity meet.
    Friday, February 13, 2009 3:34 PM
  •   Please correct me if I am missing anything on your scenario.  You added a note describing that you are also using sysadmin role. By definition, members of the sysadmin server role have all privileges on the server and cannot be denied any permission.

       From your description it is clear that your intention is not to give full control of the server, so I would suggest removing sysadmin role membership and try to find the permissions you really need.

      BTW. I would recommend reading the following technical article, it may not really be applicable in this case (since most likely you are not developing the application, but dealing with an existing one), but it should give you an insight and potential solutions for scenarios where a high-privileged operation is needed by your application.

    “Engine Separation of Duties for the Application Developer” (http://msdn.microsoft.com/en-us/library/cc974525.aspx)

      I hope this information helps,

      -Raul Garcia
       SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 13, 2009 7:23 PM