none
SQL Server Permissions. RRS feed

  • Question

  • Version - SQL Server 2014 ENT.

    Goal - To provide activity monitor access with least privilege to users. 

    Two security groups - Reader (db_datareader) and Owner (db_owner) 

    Trying to give permissions of 'View Server State' so either group of users can view activity monitor for analyze issues which comes not very frequently. At same time wan't to limit accessibility/limit to least level. Will it make any difference giving 'View Server State ' permissions to readers Vs Owners ? What permissions DBOs can get which readers can't get if permission applied to them. 

    Thanks

    Vijay 


    Tuesday, June 2, 2020 5:37 AM

Answers

  • Hi Vijay,

    According to MS document, to view actual activity, you must have VIEW SERVER STATE permission. To view the Data File I/O section of Activity Monitor, you must have CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permission in addition to VIEW SERVER STATE.

    MS document Fixed-Database Roles. Members of the db_datareader fixed database role can read all data from all user tables. If you giving 'View Server State' to db_datareader, you can’t view the Data File I/O section of Activity Monitor. 

    Best regards,
    Cathy 

    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


    Tuesday, June 2, 2020 7:31 AM
  • Two security groups - Reader (db_datareader) and Owner (db_owner) 

    Trying to give permissions of 'View Server State' so either group of users can view activity monitor for analyze issues which comes not very frequently. At same time wan't to limit accessibility/limit to least level. Will it make any difference giving 'View Server State ' permissions to readers Vs Owners ? What permissions DBOs can get which readers can't get if permission applied to them. 

    There is no difference. db_datareader and db_owner are database roles that controls permissions in the database. VIEW SERVER STATE is a server level permission.
    If you grant VIEW SERVER STATE to a user, that user can access most DMVs. If you don't want that, you cannot give them access to Activity Monitor.

    However, if you want them to be table to run a stored procedure which shows the same kind of information such as my beta_lockinfo or Adam Machanic's sp_WhoIsActive, you could sign this procedure with a certificate and create a login from that certificate which you grant VIEW SERVER STATE. Finally, you grant selected users permission on the procedure. The permission is now bundled with the procedure. I discuss this technique in a lot more detail in an article on my web site: http://www.sommarskog.se/grantperm.html.


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

    • Marked as answer by V Jay Rana Wednesday, June 3, 2020 9:52 AM
    Tuesday, June 2, 2020 9:42 PM

All replies

  • Users who are member of db_owner database role   have all permissions  on the database

    >>>What permissions DBOs can get which readers can't get if permission applied to them. 

    I am not sure that understood  the question. Are you referencing to data readers db role?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 2, 2020 6:22 AM
  • I am referring to both DB_Owner and DB_Datareader,

    Yes users with db_owner has all permissions on the database, Additionaly if I give them 'View Server State' permissions that's will give them access to more permissions (To all DMV's I guess, correct me if I am wrong) which we don't want to. So instead of giving 'View Server State' to db_owners, can it applied to another group having permissions with db_datareaders, will it make any difference ?

    Basically both reader and owner belongs to one team and they need access for activity monitor to analyse performance issues of databases in certain cases. 

    Thanks

    Tuesday, June 2, 2020 6:41 AM
  • Hi Vijay,

    According to MS document, to view actual activity, you must have VIEW SERVER STATE permission. To view the Data File I/O section of Activity Monitor, you must have CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permission in addition to VIEW SERVER STATE.

    MS document Fixed-Database Roles. Members of the db_datareader fixed database role can read all data from all user tables. If you giving 'View Server State' to db_datareader, you can’t view the Data File I/O section of Activity Monitor. 

    Best regards,
    Cathy 

    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


    Tuesday, June 2, 2020 7:31 AM
  • Two security groups - Reader (db_datareader) and Owner (db_owner) 

    Trying to give permissions of 'View Server State' so either group of users can view activity monitor for analyze issues which comes not very frequently. At same time wan't to limit accessibility/limit to least level. Will it make any difference giving 'View Server State ' permissions to readers Vs Owners ? What permissions DBOs can get which readers can't get if permission applied to them. 

    There is no difference. db_datareader and db_owner are database roles that controls permissions in the database. VIEW SERVER STATE is a server level permission.
    If you grant VIEW SERVER STATE to a user, that user can access most DMVs. If you don't want that, you cannot give them access to Activity Monitor.

    However, if you want them to be table to run a stored procedure which shows the same kind of information such as my beta_lockinfo or Adam Machanic's sp_WhoIsActive, you could sign this procedure with a certificate and create a login from that certificate which you grant VIEW SERVER STATE. Finally, you grant selected users permission on the procedure. The permission is now bundled with the procedure. I discuss this technique in a lot more detail in an article on my web site: http://www.sommarskog.se/grantperm.html.


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

    • Marked as answer by V Jay Rana Wednesday, June 3, 2020 9:52 AM
    Tuesday, June 2, 2020 9:42 PM
  • Thanks Erland for clarification. 
    Wednesday, June 3, 2020 9:53 AM