locked
Need VIEW DATABASE STATE permission for sp_spaceused in SQL Server 2017? RRS feed

  • Question

  • Hi All,

    It failed to execute sp_spaceused in SQL Server 2017, if the sql user has below permissions, but executing sp_spaceused can work well under SQL 2012, 2014 and 2016 with the same permission.

    SELECT * FROM fn_my_permissions(NULL, 'DATABASE'), the returned values:

    CONNECT, SELECT, INSERT, UPDATE, DELETE, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION, VIEW ANY COLUMN MASTER KEY DEFINITION.

    Are there any changes for SQL Server 2017?

    Thanks

    Scott

    Thursday, December 20, 2018 2:16 AM

Answers

  • For the record: I have file a bug for the issue on

    https://feedback.azure.com/forums/908035-sql-server/suggestions/36354319-sp-spaceused


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 21, 2018 9:37 PM
  • Hi Scott,

     

    The user with public role can run "exec sp_spaceused" whatever in SQL Server 2016 or 2017. But in SQL Server 2017, there will display some error messages but the stored procedure is still executed. Please refer to the screenshot in my first reply.

     

    If the user want to run " exec spspaceused @updateusage = N'TRUE' ", the db_owner role is necessary.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 21, 2018 1:41 AM

All replies

  • Hi Scott,

     

    Based on my test, whatever in SQL Server 2016 or SQL Server 2017, the user can execute sp_spaceused just with public role (just have CONNECT, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION,VIEW ANY COLUMN MASTER KEY DEFINITION permissions).

     

    But in SQL Server 2017, if the user does not have the VIEW DATABASE STATE permission, when executing sp_spaceused the error message that VIEW DATABASE STATE permission denied in database 'XX' will be displayed. But you can still click the Results tab to see the result sets.

     

     

     

    Before SQL Server 2017, there are no error messages displayed.

     

    By the way, for @updateusage parameter, only members of the db_owner fixed database role can specify it.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, December 20, 2018 9:38 AM
  • Hello Scott,

    Which error message do you get?

    See sp_spaceused (Transact-SQL) => Permissions


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, December 20, 2018 9:46 AM
  • If I get the line numbers correctly, it is this line in sp_spaceused that fails:

    		select @ckptfilesize = sum(convert(bigint,size))
    			from sys.database_files
    			where data_space_id IN (SELECT data_space_id FROM sys.data_spaces WHERE type=N'FX')
    
    

    Here is a repro:

    CREATE USER virre WITHOUT LOGIN
    go
    EXECUTE AS USER = 'virre'
    go
    EXEC sp_spaceused
    go
    REVERT
    go
    DROP USER virre
    

    I will have to admit that I find this to be a bit of a feature - I am not sure that I think just membership in public should be enough to run sp_spaceused. But as a breaking change it should be documented.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 20, 2018 11:08 PM
  • Hi Emily,

    Thanks for your help! 

    The current SQL user properties:

    - Server Roles: public

    - User Mapping: db_dataexecute, db_datareader, db_datawriter, public. (without db_owner)

    But before SQL Server 2017, sp_spaceused can work.

    We don't prefer to add the 'db_owner'. To add 'db_owner' is a big change for our production.  

    For 'db_owner' role, it has lots of permissions. It is not a good idea to add the 'db_owner' from SQL Server database customer's security consideration.

    Can I get this change for SQL Server 2017 from Microsoft Official Document? 

    Thanks

    Scott




    • Edited by ScottQ Monday, December 24, 2018 6:07 AM update DB user's consideration.
    Thursday, December 20, 2018 11:50 PM
  • Hi Olaf Helper,

    The error message is 'VIEW DATABASE STATE permission denied in database ....'.

    Thanks

    Scott

    Friday, December 21, 2018 12:08 AM

  • But before SQL Server 2017, sp_spaceused can work.

    We don't prefer to add the 'db_owner'. To add 'db_owner' is a big change for our production.  

    You don't need to grant the user "DB_Owner" to be able to run sp_spaceused. Any user with public role membership should be able to run "sp_spaceused", however, a non DB owner cannot specify the parameter "@updateusage" when running that sproc. 

    As Emily mentioned, although SQL 2017 throws the error messages, it still executes the stored procedure just as how it would on SQL 2016 or lower. Click on "Results" tab on the output pane to see the outcome. 

    Alternatively, if you are OK, grant the user "View Database State" so the error messages don't show up. 

    Use <DBName>
    GO
    Grant view database state to <Username>
    GO


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, December 21, 2018 12:38 AM
  • Hi Scott,

     

    The user with public role can run "exec sp_spaceused" whatever in SQL Server 2016 or 2017. But in SQL Server 2017, there will display some error messages but the stored procedure is still executed. Please refer to the screenshot in my first reply.

     

    If the user want to run " exec spspaceused @updateusage = N'TRUE' ", the db_owner role is necessary.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 21, 2018 1:41 AM
  • Hi Emily,

    Thank you for explanation again! 

    Yes, the results of database information also can be got after executing the sp on Microsoft SQL Server Management Studio, even if above error messages exist.

    But it failed to read the Database information via our production application.

    Maybe my boss can accept to grant 'VIEW DATABASE STATE' permission to the sql user, in order to support SQL Server 2017. It is much better if I can get Microsoft official document about this changes on SQL Server 2017.

    I really appreciate your warmly help! 

    Scott


    • Edited by ScottQ Friday, December 21, 2018 3:12 AM
    Friday, December 21, 2018 2:49 AM
  • Hi Scott,

     

    >>Maybe my boss can accept to grant 'VIEW DATABASE STATE' permission to the sql user, in order to support SQL Server 2017. It is much better if I can get Microsoft official document about this changes on SQL Server 2017.

     

    Thanks for your reply.

     

    I will also give the feedback to someone related. You can also create the feedback about this scenario on the website. https://feedback.azure.com/forums/908035-sql-server

     

    Your feedback enables Microsoft to offer the best software and deliver superior services.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 21, 2018 3:26 AM
  • As Emily mentioned, although SQL 2017 throws the error messages, it still executes the stored procedure just as how it would on SQL 2016 or lower. Click on "Results" tab on the output pane to see the outcome. 


    That depends. If you run with XACT_ABORT ON (and which I have checked to be on in the options in SSMS), you don't get any result sets.

    It goes without saying that the results you get may be incorrect. Looking at the code, this seems to be particularly true if the database has memory-optimized tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 21, 2018 8:29 AM
  • For the record: I have file a bug for the issue on

    https://feedback.azure.com/forums/908035-sql-server/suggestions/36354319-sp-spaceused


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 21, 2018 9:37 PM