Read Only Access RRS feed

  • Question

  • Hi All,

    We have to grant read only access on the MSSQL Instance & databases  for checking the locks, activity monitor, profiler. Is there any way other that granting sysadmin. Please suggest.

    I had read in a blog that granting VIEW SERVER STATE to login would be sufficient. Please let me know if it is okay.



    Tuesday, November 3, 2015 8:26 AM


  • It is unclear what you are trying to achieve. Do you want to set up database to read only state or you want to grant read only access to the user?

    GRANT EXEC ON some_sp TO user-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user-- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO user-- Grant EXEC permission all procedures in the database.

    To grant all "normal" permissions on a table

       GRANT SELECT on tbl TO user

    To grant freddie access on tables in a schema:

       GRANT SELECT on SCHEMA::dbo TO user

    To grant him read access on all tables:

       GRANT SELECT TO user

    Best Regards,Uri Dimant SQL Server MVP,

    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, November 3, 2015 10:03 AM