Tag: T-SQL Script

Script to Find the SQL Agent Job Name in Execution

In initial days I had to scratch my head for a long time to find out the SQL Agent Job Name in Execution just to provide details to customer or to find out the job causing issues.
The below simple query will be helpful in the following situations:

  • Several agent jobs showing in “sp_who2 active” results set and you want to identify them.
  • You have identified the root blocker as a SQL Agent job and need the problem job name to notify Customer.
  • Customer requested to find out the status of a job in progress etc.
sp_who2 active output does not show the job name:

Image of Sp_who2 active output which does not show job name

The below simple query will help you find the information:
SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(<jobid> AS UNIQUEIDENTIFIER)

Considering the above example:

SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(0xD81B43320D3F2843B9A6A40CA120F14B AS UNIQUEIDENTIFIER)

Image of the query result showing job name

Let me know if it helps and any suggestions are always welcome.
You may also find the below scripts useful:
List all permissions for a user in all or selective databases
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

{ Add a Comment }

List all permissions for a user in all or selective databases

Today I am gonna talk about the requirement of finding out all permissions for a user in all or selective databases.

The query here will help you in following situations:

  1. Customer has provided you with a user id and couple of database names and they wanted to find out all the permissions the user has on those databases.
  2. You have a task to provide access for a user id to some of the databases. You have provided the access and now want to pull the report to send to customer.
  3. You have been given a user id and asked to find out all permissions in all user databases.

This may not be exactly what you are looking for but certainly will give you some idea to make it your way.

Retrieve all permissions for a user in selective databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.sys.databases
WHERE name IN ('database1','database2') --Obviously you’ll put your database names 
AND state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name =''Test_User'''  --The user you want to find permissions for

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

In this example I have listed the permissions for “Test_User” on databases “database1” and “database2″as shown the below screen shot:

Retrieve all permissions for a user in all user databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb') 
AND state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'SELECT

 ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name=''Test_User'''

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor 

Here is an example output:

Please feel free to let me know if there is any better way you can think of to retrieve the same information.

Check out the below scripts as well:
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name prior to Failover
Script to List All Users with db_owner role in SQL 2000

{ Add a Comment }

Script to retrieve database backup information

Backup script is something which is a very common need of a DBA in day to day life.This article will provide you with a base query to retrieve database backup information.

I am not able to give credit to anybody as I have seen lot of similar queries in the internet and so could not find the original writer.

Hmm! then what is the need of having another one in this blog? Well, I have experienced that sometimes a simple query suffice your need rather than a fancy one.

The below queries will help answering  the following questions:
  • Do you have recent database backups?
  • What type of backup is being taken for particular databases?
  • What is the backup size?
  • Who has taken the backup or which application login id is being used to take backup?
  • What is the Recovery model of the databases being backup up?
  • How long it took to backup?
Backup details of all the databases of all backup types:
SELECT server_name, database_name, 
CASE 
   WHEN TYPE='D' THEN 'Full Backup' 
   WHEN TYPE='L' THEN 'Log Backup'
   WHEN TYPE='I' THEN 'Diff Backup' 
END AS BackupType, 
backup_start_date, backup_finish_date, DATEDIFF(SS,  backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', USER_NAME AS UserName  
FROM msdb..backupset 
ORDER BY backup_finish_date DESC 
The following screen shot is to show you an example output:

You can use this as a base and tweak it here and there as per your requirements. Few examples are given below.

Retrieve the same information for particular database(s) and for a particular type:
SELECT server_name, database_name, 
CASE 
   WHEN TYPE='D' THEN 'Full Backup' 
   WHEN TYPE='L' THEN 'Log Backup'
   WHEN TYPE='I' THEN 'Diff Backup' 
END AS BackupType, 
backup_start_date, backup_finish_date, DATEDIFF(SS,  backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', USER_NAME AS UserName  FROM msdb..backupset
WHERE database_name IN ('master','model','msdb')
AND TYPE='D' 
ORDER BY backup_finish_date DESIC 
Include recovery model of the databases in the same output list:
SELECT b.server_name, b.database_name, 
CASE 
   WHEN TYPE='D' THEN 'Full Backup' 
   WHEN TYPE='L' THEN 'Log Backup'
   WHEN TYPE='I' THEN 'Diff Backup' 
END AS BackupType, 
d.recovery_model_desc AS RecoveryModel,
b.backup_start_date, b.backup_finish_date, DATEDIFF(SS,  b.backup_start_date, b.backup_finish_date)/60 AS [Duration(Minutes)],
CAST(b.backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', b.USER_NAME AS UserName  FROM msdb..backupset b
JOIN sys.databases d
ON b.database_name = d.name
WHERE database_name IN ('master','model','msdb')
AND TYPE='D' 
ORDER BY backup_finish_date DESC

Please let me know if it helps by leaving your comments below.

Further more you can go through the below link for some addition scripts:

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

You might also want to look at the below scripts:
Script to get Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

{ Add a Comment }

Script to List All Users with db_owner role in SQL 2000

The Requirement was to provide a report to list all users with db_owner role. No big deal right? But the fun part was there were many servers running SQL 2000 version. Of course first thing first, search in the internet. Got few but not quite which I was looking for.

So ultimately I wrote the below query to provide the required details in SQL 2000. I have kept the option open to list not only for db_owner but also for any other database role.
You can tweak it as per your requirements.

SELECT p.name AS UserName,
      CASE WHEN p.isntgroup =1 AND p.isntuser=0 THEN 'Windows Group'
           WHEN p.isntgroup =0 AND p.isntuser=1 THEN 'Windows Login'
           WHEN p.issqlrole=1 THEN 'Database Role'
      ELSE 'SQL Login' END AS 'Login Type',
      USER_NAME(roles.groupuid) AS 'AssociatedRole' 
      FROM sysmembers roles
           JOIN sysusers p ON roles.memberuid = p.uid
           JOIN sysusers pp ON roles.groupuid = pp.uid AND  p.uid NOT IN (0,1,2,3,4) AND
           USER_NAME(roles.groupuid) IN ('db_owner', 'db_datareader')

The following screen shot shows the output:

I hope this helps. This post can only be treated as complete once you put your thoughts in the comment section.
You might also want to check the link which includes video to know the Myth around Remote Query Timeout Option

{ Add a Comment }