locked
sp_updatestats fails to run with 'permission' error under dbo-priveleged user RRS feed

  • Question

  • Hi.

     

    I'm trying to run sp_updatestats by user having db_owner membership in current database. According to BOL 'sp_updatestats' entry:

    Requires membership in the sysadmin fixed server role, or ownership of the database (dbo).

     

    Still i receive error:

    Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15

    User does not have permission to perform this action.

     

    Hmm. Is it some sort of bug? Or do i miss anything?

     

    p.s.

    select @@version

    Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86)   Mar  2 2007 20:01:28   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Sunday, July 27, 2008 11:29 AM

Answers

  • I ran into the same thing this morning.  The thread below suggests it's a bug in sp_updatestats.  Apparently the sp is looking for the account to run sp_updatestats to be a member of sysadmin server role, or, THE dbo rather than a member of db_owner database role. 

     

    http://www.eggheadcafe.com/forumarchives/SQLServerprogramming/Jun2005/post23402374.asp

     

    Monday, July 28, 2008 3:26 PM
  • Another option is to create a stored procedure using the "execute as" clause and assign exe permission to the user that needs to update stats:

     

    create procedure dbo.up_updstats

    with execute as 'dbo'

    as

    exec sp_updatestats

    go

    grant execute on dbo.up_updstats to [<user>]

    go

     

    cheers

    Tuesday, July 29, 2008 6:19 AM

All replies

  • Assure that the user is a member of the sysadmin role:

     

    Code Snippet

    WITH roles

    AS

    (

    SELECT role_principal_id rlid

    FROM sys.server_role_members sm JOIN sys.server_principals sp

    ON sm.member_principal_id = sp.principal_id

    WHERE sp.[name] = (SELECT SUSER_NAME())

    )

    SELECT [name]

    FROM sys.server_principals sp

    WHERE principal_id = (SELECT rlid FROM roles)

     

     

    Sunday, July 27, 2008 1:29 PM
  •  

    User is NOT a member of sysadmin server role, i can tell you without scripts Smile

    Well, script returned empty resultset anyway.

     

    As i've mentioned earlier, user is member of db_owner though. And according to BOL that should be enough.

    • Proposed as answer by Vrell Wednesday, April 5, 2017 4:09 PM
    Monday, July 28, 2008 5:15 AM
  • I ran into the same thing this morning.  The thread below suggests it's a bug in sp_updatestats.  Apparently the sp is looking for the account to run sp_updatestats to be a member of sysadmin server role, or, THE dbo rather than a member of db_owner database role. 

     

    http://www.eggheadcafe.com/forumarchives/SQLServerprogramming/Jun2005/post23402374.asp

     

    Monday, July 28, 2008 3:26 PM
  • Another option is to create a stored procedure using the "execute as" clause and assign exe permission to the user that needs to update stats:

     

    create procedure dbo.up_updstats

    with execute as 'dbo'

    as

    exec sp_updatestats

    go

    grant execute on dbo.up_updstats to [<user>]

    go

     

    cheers

    Tuesday, July 29, 2008 6:19 AM