Archive for August, 2013

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

 

How to query the job execution durations for the past 2 weeks?

You need to find the job execution durations for each of the jobs for the past 2 weeks – pronto! Your boss or client doesn’t want to hear it or care about why it may be a challenge as the run_duration column in the msdb..sysjobhistory system table is formatted in HHMMSS and it is based on the job steps and you will need to join it to the msdb..sysjobs system table to perform complicated subqueries to get the results.  What do you do – hot shot? What do you do?

You can start thinking about deploying diplomacy or beat around the bushes. But, please don’t do it, people will lose confidences in your capability, and that’s reserved for the politicians only not SQL Server DBAs. First, let’s remind yourself of who you are, be proud about it, and then let’s run this script:

-- query job execution durations for the past 2 weeks
select serverproperty('servername') as InstanceName, job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
where DATEDIFF(DAY, run_datetime, GETDATE()) < 15 -- search for the # of days you wish to see the job histories
order by job_name, run_datetime

Please note that the original script was copied from http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx. I have modified it to add the instance name and filter the query to search for the past 14 days so that you can run it for multiple instances of SQL Server using a multi-server query.

I hope this will help you out of a jam and save you a few hours of creating your own script so that you can at least enjoy the 4th quarter of a Monday Night Football game.

 

REFERENCES:

SQL Server Script to Display Job History
http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx

dbo.sysjobhistory
http://technet.microsoft.com/en-us/library/ms174997.aspx

DATEDIFF()
http://technet.microsoft.com/en-us/library/ms189794.aspx

How to find the most recent updates for a table?

You wish to find the most recent updates for a table or for all tables in a SQL Server database, you wanted to know if the data are being updated at all. How can you do it as quickly as possible? Yes. There’s a Dynamic Management View for that and it is: sys.dm_db_index_usage_stats.

 

Here’s an example of how you can use it and verify it:

  1. Create a test table.
  2. Insert some data into the test table.
  3. Query the date and time the data is inserted.
  4. Query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table.
  5. Update the table by modifying data.
  6. Query the date and time the data is updated.
  7. Query the sys.dm_db_index_usage_stats DMV to confirm the most recent update to the table.

Example Code:

USE AdventureWorks2012
GO
-- create a test table
CREATE TABLE VeryTalentedPeople
(
ID INT identity (1,1)
, FIRSTNAME VARCHAR(100)
, LASTNAME VARCHAR(100)
)
GO

-- insert some data into the table
INSERT INTO VeryTalentedPeople
VALUES ('Barak','Obama')
, ('Ellie', 'Goulding')
, ('Katy', 'Perry')
, ('Lady','Gaga')
GO

-- query the date and time the data is inserted
SELECT GETDATE()

-- query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks2012')
AND OBJECT_ID=OBJECT_ID('VeryTalentedPeople')

-- update the table by modifying data
UPDATE VeryTalentedPeople
SET FIRSTNAME = 'Stefani'
, LASTNAME = 'Germanotta'
WHERE FIRSTNAME = 'Lady'
and LASTNAME = 'Gaga'

-- query the date and time the data is updated
SELECT GETDATE()

-- query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks2012')
AND OBJECT_ID=OBJECT_ID('VeryTalentedPeople')

REFERENCES:

sys.dm_db_index_usage_stats
http://msdn.microsoft.com/en-us/library/ms188755.aspx

How to manage terminal services sessions remotely?

Have you ever had to remote into a server to check the Windows server settings or local group policies setting that can’t be done through SQL Server Management Studio (SSMS), but unable to log in as the server had exceed the maximum number of 2 sessions? Frustrating isn’t it? It will make you even more furious if you knew one of your colleagues had forgotten to log off as he was in a hurry to head home to watch Monday Night Football or run off for a midday hour break for Krispy Kreme doughnuts.

No need to get angry my friends as that will add more stress to our lives and turn us into grumpy DBAs. There’s hope and solution for this issue after all. QWINSTA and RWINSTA to the rescue and their definitions are as follows:

  • QWINSTA: Query WINdows STAtion
  • RWINSTA: Reset WINdows STAtion

Without further talking like a politician and providing no practical solutions, here’s how you can use them:

  1. Run the command: QWINSTA /server:xxx.xxx.xxx. Where xxx.xxx.xxx is the ip address you wish to RDP into. You can also key in the server name instead.
  2. Look for the USERNAME, ID, and STATE columns. Mark the ID for the connection your wish to reset (kill). Usually, it is the one with the STATE of disconnected (Disc). If you must reset one that’s active, you should pick up the phone and call that individual. Be careful not to reset your boss’s connection!
  3. To kill the session that you had identified, run the command RWINSTA ID /Server:xxx.xxx.xxx. Where xxx.xxx.xxx is the ip address of the server and ID is the session ID you wish to reset.

QWINSTA and RWINSTA