How to grant view only permission to users to see SQL Server Agent jobs and read only access in all user databases?

As per industry best practices, your SQL Servers are divided into separate environments: Development, Staging, and Production. Sure, there can be more layers such as Quality Assurance (QA), User Acceptance and Testing (UAT), Pre-Production (Pre-Prod), and etc. As per the best security practice, only the Production SQL Server DBA team will have full access (sys admin access) to the production servers. Through a change request process, the Development team has submitted a request to view the SQL Server Agents jobs and have read access to all tables in all user databases. The request has been approved by management. Now, it’s your turn to grant the least privileges required for the Development team to view and SQL Server Agent jobs and all user databases objects. How can you do so without over granting access privileges? You can do so by granting the login(s) to the SQLAgentReaderRole database role in the msdb and the db_datareader database role in all databases.

There are 3 SQL Server Agent fixed database roles in SQL Server. They are:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

The SQLAgentUserRole allows users to create jobs and manage the jobs that they create. When the users are granted to SQLAgentReaderRole, they will be able to see the jobs, job histories, even job that the users do not own. However, the users belonging to the SQLAgentReaderRole only cannot execute the jobs. The SQLAgentOperatorRole allows users all the rights to the SQLAgentReaderRole plus permissions to execute the jobs that they do not own. So, if you wish to grant the user(s) permission to view the jobs only in the production server, you will grant the user(s) to the SQLAgentReaderRole in the msdb.

The db_datareader database role in each database enables the members in the role to run SELECT statement on any tables or view in the database.  Just select or view only, nothing more.

You may also consider granting the development team the VIEW SERVER STATE permission in the master database so that they can see server level configuration information and also the VIEW DEFINITION on the user databases so that they can see the TSQL code and the metadata for the objects in each user databases.

We will walk-through the following steps:

  1. Create a test login. In this case: BillyBob.
  2. Log in as BillyBob and try to view the jobs and job histories. It will fail as BillyBob has not been added to the SQLAgentReaderRole. BillyBob can’t even see the SQL Server Agent.
  3. Log in as a sys admin, add BillyBob to the SQLAgentReaderRole.
  4. Log in as BillyBob and try to view the jobs and job histories. It will succeed this time as BillyBob has been added to the SQLAgentReaderRole. BillyBob can only view the jobs and job histories but can’t delete or run any jobs.
  5. Log in as a sys admin, create the user BillyBob in all databases in the instance and then add BillyBob to the db_datareader database role in all user databases. BillyBob will then be able to select data on the user databases.
  6. Log in as a sys admin, grant BillyBob the VIEW SERVER STATE permission in the master database so that BillyBob can see server level configuration information.
  7. Log in as a sys admin, grant BillyBob the VIEW DEFINITION privilege on all user databases for BillyBob to see the code and metadata for all objects in each user databases.

Implementation Steps:

  1. Create a test login. In this case: BillyBob.
    -- create the test login
    CREATE LOGIN BillyBob WITH PASSWORD = 'L0n3Ran8er'
    
  2. Log in as BillyBob and try to view the jobs and job histories. It will fail as BillyBob has not been added to the SQLAgentReaderRole. BillyBob can’t even see the SQL Server Agent.
  3. Log in as a sys admin, add BillyBob to the SQLAgentReaderRole.
    -- add BillyBob to the msdb database
    USE msdb
    GO
    CREATE USER BillyBob FOR LOGIN BillyBob
    
    -- add BillyBob to the SQLAgentReaderRole
    use msdb
    EXECUTE sp_addrolemember
    @rolename = 'SQLAgentReaderRole',
    @membername = 'BillyBob'
    
  4. Log in as BillyBob and try to view the jobs and job histories. It will succeed this time as BillyBob has been added to the SQLAgentReaderRole. BillyBob can only view the jobs and job histories but can’t delete or run any jobs.
  5. Log in as a sys admin, create the user BillyBob in all databases in the instance and then add BillyBob to the db_datareader database role in all user databases. BillyBob will then be able to select data on the user databases.
    -- create the user BillyBob in all databases in the instance
    EXECUTE master.sys.sp_MSforeachdb 
    'USE [?];
    CREATE USER BillyBob FOR LOGIN BillyBob
    '
    
    -- add the user BillyBob to the db_datareader database role in all user databases
     EXECUTE master.sys.sp_MSforeachdb 
    'IF ''?'' NOT IN (''tempDB'',''model'')
    BEGIN 
    USE [?]
    EXEC sp_addrolemember ''db_datareader'', ''BillyBob''
    END
    '
    
  6. Log in as a sys admin, grant BillyBob the VIEW SERVER STATE permission in the master database so that BillyBob can see server level configuration information.
    -- grant BillyBob the VIEW SERVER STATE permission in the master database
    USE master
    GO
    GRANT VIEW SERVER STATE to BillyBob
    
  7. Log in as a sys admin, grant BillyBob the VIEW DEFINITION privilege on all user databases for BillyBob to see the code and metadata for all objects in each user databases.
    -- grant BillyBob the VIEW DEFINITION to all user database
     EXECUTE master.sys.sp_MSforeachdb 
    'IF ''?'' NOT IN (''master'',''tempDB'',''model'',''msdb'')
    BEGIN 
    USE [?]
    GRANT VIEW DEFINITION TO BillyBob
    END
    '
    

BillyBob will now have the required privileges to view the SQL Server Agent jobs, select data from all tables and views from all user databases, and view the code and metadata in all user databases.

REFERENCES:

SQL Server Agent Fixed Database Roles
http://technet.microsoft.com/en-us/library/ms188283.aspx

VIEW DEFINITION Permission
http://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx

CREATE LOGIN
http://technet.microsoft.com/en-us/library/ms189751.aspx

CREATE USER
http://technet.microsoft.com/en-us/library/ms173463.aspx

sp_addrolemember
http://technet.microsoft.com/en-us/library/ms187750.aspx

db_datareader
http://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

 

Be Sociable, Share!
Responses are currently closed, but you can trackback from your own site.

One Response to “How to grant view only permission to users to see SQL Server Agent jobs and read only access in all user databases?”

  1. [...] How to grant view only permission to users to see SQL … – How to grant view only permission to users to see SQL Server Agent jobs and read only access in all user databases?… [...]