none
Permissions on public role override explicit permissions for login

    Question

  • Hi,

     

    I have a problem with conflicting permissions for a stored procedure.

     

    The sql login "labmanager" has Grant ticked for the Execute permission of the sproc.

    However, the "public" role has Deny ticked for the Execute permission.

     

    As a result, the permissions on the public role are overriding the permissions of the labmanager login. If I remove the permission for the public role, everything behaves as normal.

     

    Is there a way to either remove labmanager from the public role, or otherwise make the permissions work correctly so that the permissions for the login take precedence?

     

    This is using SQL Server 8.0.2050 on a Windows 2003 Server.

     

    Many Thanks,

     

    Jessica

    Wednesday, October 15, 2008 3:15 AM

Answers

  • Hi, Jessica,

         The reason why "labmanager" can't execute the sproc is because a denied permission always takes precedence. Denied permission at any level (user, group, or role) denies the permission on the object regardless of existing granted or revoked permissions for that user. (See http://msdn.microsoft.com/en-us/library/aa905174(SQL.80).aspx).

        It is unable to remove labmanager from public role. As you know, Execute permissions of sproc are already granted to the public role, but it doesn't mean that everybody can execute the sproc. After EXECUTE has been issued, the system stored procedures check the user's role membership. If the user is not a member of the appropriate fixed server or database role necessary to run the stored procedure, the stored procedure will not continue. (See http://msdn.microsoft.com/en-us/library/aa905175(SQL.80).aspx).

       I hope it could help you to resolve your problem.

     

    Thanks

     

    Gary

    Wednesday, October 15, 2008 4:38 AM

All replies

  • Hi, Jessica,

         The reason why "labmanager" can't execute the sproc is because a denied permission always takes precedence. Denied permission at any level (user, group, or role) denies the permission on the object regardless of existing granted or revoked permissions for that user. (See http://msdn.microsoft.com/en-us/library/aa905174(SQL.80).aspx).

        It is unable to remove labmanager from public role. As you know, Execute permissions of sproc are already granted to the public role, but it doesn't mean that everybody can execute the sproc. After EXECUTE has been issued, the system stored procedures check the user's role membership. If the user is not a member of the appropriate fixed server or database role necessary to run the stored procedure, the stored procedure will not continue. (See http://msdn.microsoft.com/en-us/library/aa905175(SQL.80).aspx).

       I hope it could help you to resolve your problem.

     

    Thanks

     

    Gary

    Wednesday, October 15, 2008 4:38 AM
  • So because the user the app uses is probably in the db_owner role, it doesn't get denied by the permission on the public role.

     

    I also found that if using a Windows login, if that account was in the Administrators group for that computer, then it no longer had the permission denied error. So being a local admin elevates a windows login to db_owner or other role automatically, avoiding the permission on the public role also?

     

    It seems to make sense now.

     

    Thanks,

     

    Jessica

     

    Saturday, October 18, 2008 3:06 AM