Microsoft – SQL Server – Permissions – “View Database State”

Background

In our last post we spoke a bit about the “View Database State” permission.

In this post, we will explore it a bit further and attempt to determine it’s use case.

 

Create Database User and grant it “Database View State” permission

 

Let us create the user and grant the designated permission.

 


set noexec off
go

USE [master]
GO

if  suser_sid('viewdatabasestate') is not null
begin
    set noexec on
end
go

CREATE LOGIN [viewdatabasestate] WITH PASSWORD=N'hello'
        , DEFAULT_DATABASE=[master]
        , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

set noexec off
go

if db_id('DBLAB') is null
begin
    exec('create database [DBLAB]')
end
go

if db_id('DBLAB') is null
begin
    set noexec on
end
go

use [DBLAB]
go

if  user_id('viewdatabasestate') is not null
begin

    set noexec on

end
go

create user [viewdatabasestate] from LOGIN [viewdatabasestate];
go

set noexec off
go

grant view database state to [viewdatabasestate]
go

set noexec off
go

 

List Database Level Permissions Grantees

Let us list which users have Database level permissions.

 


 select
          p.class_desc as [permissionClass]
        , p.[type] as [permissionType]
        , p.[permission_name] as [permissionName]
        , p.[state_desc] as [permissionState]
        , user_name(p.grantee_principal_id) as DBUser
        , SUSER_SNAME(dp.sid) as SQLInstanceUser

 from sys.database_permissions p

           inner JOIN sys.database_principals dp

               on p.grantee_principal_id = dp.principal_id

 where p.class_desc = 'DATABASE'

Image:

 

viewDatabaseState

 

We can see that our SQL Login (viewdatabasestate) has “view database state” permission.

 

DATABASE VIEW STATE – Review use case

 

What does it buy us?

 

Check for missing indexes

 

Let us see if it allows us to check for missing indexes

 


/*
    Check for missing indexes
*/
SELECT
          d.[object_id],
          s = OBJECT_SCHEMA_NAME(d.[object_id]),
          o = OBJECT_NAME(d.[object_id]),
          d.equality_columns,
          d.inequality_columns,
          d.included_columns,
          s.unique_compiles,
          s.user_seeks,
          s.last_user_seek,
          s.user_scans
          , s.last_user_scan
FROM sys.dm_db_missing_index_details AS d
        INNER JOIN sys.dm_db_missing_index_groups AS g
            ON d.index_handle = g.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats AS s
            ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0;

 

Output:

 

TheUserdoesnothavepermissiontoperformthisaction

 

 

Check for index fragmentation

Let us see if it allows us to check for index fragmentation

SELECT
            db_name() as databaseName
          , OBJECT_NAME(indexStats.OBJECT_ID) AS TableName
          , ind.name AS IndexName
          , indexstats.index_type_desc AS IndexType
          , indexstats.avg_fragmentation_in_percent 

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 

        LEFT OUTER JOIN sys.indexes ind
            ON indexstats.object_id  = ind.object_id
            AND indexstats.index_id = ind.index_id

 

Output:
indexFragmentationStats

 

Check most expensive queries:


Let us borrow Michael K. Campbell query for getting most expensive queries:

Performance Tip: Find Your Most Expensive Queries
Sep 13, 2012 by Michael K. Campbell in Practical SQL Server
http://sqlmag.com/blog/performance-tip-find-your-most-expensive-queries

 


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
core AS (
           SELECT
                eqp.query_plan AS [QueryPlan],
                ecp.plan_handle [PlanHandle],
                q.[Text] AS [Statement],
                n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS OptimizationLevel ,
                ISNULL(CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') as float),0) AS SubTreeCost ,
                ecp.usecounts [UseCounts],
                ecp.size_in_bytes [SizeInBytes]
           FROM
                sys.dm_exec_cached_plans AS ecp
                CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp
                CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) AS q
                CROSS APPLY query_plan.nodes ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn ( n )
        )

SELECT TOP 100
        QueryPlan,
        PlanHandle,
        [Statement],
        OptimizationLevel,
        SubTreeCost,
        UseCounts,
        SubTreeCost * UseCounts [GrossCost],
        SizeInBytes
FROM
        core
ORDER BY
        GrossCost DESC
        --SubTreeCost DESC

 

Unfortunately, it too returns “user does not have permission“.

TheUserdoesnothavepermissiontoperformthisaction

 

Check SYS Schema

Let us check sys schema objects such as sys.indexes ….

First, please grant read permissions on the sys schema to viewdatabasestate.

 

GRANT SELECT ON SCHEMA ::sys TO [viewdatabasestate]
go

 

Then let us try to list indexes

 

select *
from   sys.indexes ind 

 

But, we got back an empty list.

 

The only way to get data returned is to grant read permissions on the actual objects\tables or grant

db_datareader role ( which lets the grantee read all tables ).

 

--grant read to each object
grant select on dbo.custList TO [viewdatabasestate]

--add db_datareader role
EXEC sp_addrolemember 'db_datareader', [viewdatabasestate]

 

 

Check Table Statistics

Let us check for Statistics

 

Query:

SELECT 
          OBJECT_NAME(object_id) AS ObjectName
         ,STATS_DATE(object_id, stats_id) AS StatisticsDate
         , *
FROM sys.stats

 

Output:

viewObjectStatistics

 

Output:

No errors, but still empty list.

 

db_datareader permission

 

To view object level statistics one needs db_datareader role permission.

 


   EXEC sp_addrolemember 'db_datareader', [viewdatabasestate]

Needing additional permissions has its own problems in terms of “separation of concerns”; having insight into performance metrics is sometimes useful to satisfy monitoring requirements, and one might be reticent to grant additional permissions.

 

Summary

So Database View State allows us to check for index fragmentation, but we are unable to check for missing indexes, table statistics,  nor review the Instance’s  query plans.

The error message does not imbue us; as it is a generic “The user does not have permission to perform this action.”

 

 

Listening To

 

I am here listening to 3 of my favorites – David Foster, Michael Buble, and Blake Shelton

Michael Buble and Blake Shelton – Home ( Live 2008 ) HD

https://www.youtube.com/watch?v=Q33YUxnGvwE

 

 

Addendum

 

August 6th,2014

The error message was enhanced sometimes between v2005 and v2014.


Msg 300, Level 14, State 1, Line 7
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 7
The user does not have permission to perform this action.

 

 

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s