locked
db_owner permissions changed in sql 2000 RRS feed

  • Question

  • A few days ago certain applications started failing. A bit of research found out that the application's user, who had dbo_owner membership could no longer execute sp_rename.

     

    As an emergency fix, I added the user to masterdb, and specifically gave execute rights to that sp. This worked. Further research has shown that apparently ALL 'db_owner' users have lost this permission and apparently lost some other built in sp execute permissions as well.

     

    This is really scarey, because we don't even know what rights or users are affected that will be a problem. How could a fixed db role be changed like that?

     

    I have no idea where to begin to look for this.

     

    Jay

     

    Tuesday, November 18, 2008 7:46 PM

Answers

  • By default, public has permission to execute sp_rename and the SP will check for the proper permissions as part of the module execution:

    SELECT user_name(grantee_principal_id) as grantee_principal_name, user_name(grantor_principal_id) as grantor_principal_name, permission_name, state_desc FROM sys.database_permissions where major_id = object_id( 'sp_rename' )

     

    Should return

    public    dbo   EXECUTE    GRANT

     

     From your description, it seems like the system administrator for this instance explicitly changed the permissions for sp_rename. I would recommend contacting your system administrator and find out if any of the builtin permissions were modified recently.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, November 20, 2008 8:43 PM

All replies

  • What was the user message / number / state ?

     

     

    Jens K. Suessmeyer

     

    Tuesday, November 18, 2008 8:38 PM
  •  

    Server: Msg 229, Level 14, State 5, Procedure sp_rename, Line 448
    EXECUTE permission denied on object 'sp_rename', database 'master', owner 'dbo'.
    Wednesday, November 19, 2008 2:50 PM
  • By default, public has permission to execute sp_rename and the SP will check for the proper permissions as part of the module execution:

    SELECT user_name(grantee_principal_id) as grantee_principal_name, user_name(grantor_principal_id) as grantor_principal_name, permission_name, state_desc FROM sys.database_permissions where major_id = object_id( 'sp_rename' )

     

    Should return

    public    dbo   EXECUTE    GRANT

     

     From your description, it seems like the system administrator for this instance explicitly changed the permissions for sp_rename. I would recommend contacting your system administrator and find out if any of the builtin permissions were modified recently.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, November 20, 2008 8:43 PM
  •  

    The query above seems to work in SQL2005 but not SQL2000 (the affected machine)

     

    Supposedly no permissions were changed.

     

    There are a number of strange things.

     

    1) it's not just sp_rename, we've seen it happen as well with other builtin sp's that should have been available.

     

    2) it has affected (apparently) all dbo rights accounts.

     

    3) As I understand, if there were an explicit group deny on a sp, then simply checking 'allowed' in EM should not over ride it (there is no red X in any of these execute permissions using EM). As indicated in my first post, if I add the user to master, then check 'allow' , he is able to excute just fine.

     

     

     

    Friday, November 21, 2008 2:17 PM
  •   You are right, the query I submitted doesn’t work for SQL Server 2000, unfortunately there is no direct equivalent in SQL Server 2000. Instead try the following one:

    EXEC sp_helprotect 'sp_rename'

     

     The results for a clean system should still the same: public has permission to execute this module

    dbo        sp_rename         public    dbo        Grant                    Execute                .

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Tuesday, November 25, 2008 12:22 AM