1

Microsoft SQL Server 2017 (RTM-CU16)

When trying to update statistics under a user which is a member of db_owner database role via

EXEC sp_updatestats;

SQL Server throws an error:

Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15 [Batch Start Line 0] User does not have permission to perform this action.

Even though MS here states that "ownership of the database (dbo)" is enough seems that it is not.

5

You misunderstood the phrase

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

"ownership of the database" means that this login owns this database.

This is not the same as "is a member of db_owner database role"

Owner of a database is one single login, while there may be as many db_owners as you want.

And in fact if you execute

exec sp_helptext 'sp_updatestats';

in the first rows you'll find:

declare @dbsid varbinary(85)

select @dbsid = owner_sid
    from sys.databases
    where name = db_name()

-- Check the user sysadmin
if not is_srvrolemember('sysadmin') = 1 and suser_sid() <> @dbsid
begin
    raiserror(15247,-1,-1)
    return (1)
end

This controls if the executor is a member of sysadmin server role or he owns this database: suser_sid() <> @dbsid where @dbsid is owner_sid of current database.

To find out the owner of some database you should execute

select owner_sid
from sys.databases
where name = 'some_db';

And you also can find it using SSMS:

enter image description here

| improve this answer | |
-2

Occurs that this sp_updatestats behaviour is some kind of a bug. One way to solve it is to use another sp and then grant the user execute permission (if not db_owner):

CREATE PROCEDURE dbo.updstatistics
WITH EXECUTE AS 'dbo'
AS
     EXEC sp_updatestats;
GO
GRANT EXECUTE ON dbo.updstatistics TO [<user>];
GO
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.