Script To Provide Database Restore Report

This article is about a script to provide database restore report for all or selective databases.

How many of you got requests to restore database from one environment to other? Yeah, I know it is one of those common activities in day to day life of a DBA. If I ask how many of you had to provide a restore report of a single database? There will be many of you have an assertive answer.

But one of the not so common request is to provide restore report of all databases. Here I am going to provide the queries to pull restore report of all and selective databases. First of all lets see the restore report of all the databases.

Furthermore the result set is more useful if you get the following fields: Source Instance, Destination Instance, Source Database Name, Destination Database Name, Restore Type, Restore Date, the login id/user name who performed the restore and the backup file name used in the restore.

Note: I have commented out few fields in the query which are not so important in all situations. You may need to uncomment them as per your requirement.

Query to provide database restore report of all databases
SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, 
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, 
CASE WHEN rh.restore_type='D' THEN 'FULL'
     WHEN rh.restore_type='L' THEN 'Log'
     WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc, 
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN MASTER.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
ORDER BY restore_date DESC
The sample result set:Script to provide database restore report

Most noteworthy field in the above result set is backup file name. The complete path with the file name shown is the location where backup was taken and not the path from where the restore files was picked. In other words if you take a backup on X location and then copy the backup file to Y location and restore process picked the file from Y location the result set will show the X location. Hope I am clear enough.

Query to provide database restore report of single or few databases
SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, 
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, 
CASE WHEN rh.restore_type='D' THEN 'FULL'
     WHEN rh.restore_type='L' THEN 'Log'
     WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc, 
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN MASTER.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
WHERE rh.destination_database_name IN ('Test_4','Test_restore')  --Put the database names
ORDER BY restore_date DESC
Result Set:
Script To Provide Database Restore Report

Hope this is helpful. Please feel free to let me know your thoughts by leaving your comments below.
You may like the below articles as well:

Script to List All Users with db_owner role in SQL 2000
List all permissions for a user in all or selective databases
Script to Rename SQL Agent Jobs
Script to find the SQL Agent Job Name in Execution
List of Users with db_owner Role in All Databases

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/restore_report/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Script to Rename SQL Agent Jobs

This article is about a script to Rename SQL Agent Jobs. First of all let me tell you the requirement.

The environment had a dedicated database for DBA’s which is used for several database maintenance and hence had a bunch of SQL Agent jobs associated. I am sure some of you also have similar setup.

Project team came up with an updated version. As a result in the upgrade process we had to rename all the jobs. I am sure you don’t wanna do it manually for each instance. Especially if you have more than 100 instances 🙂

The below queries will help in following conditions:
  • Rename SQL Agent Jobs having particular naming convention
  • Change the name of SQL Agent Jobs by removing first few characters

Note: The queries can not be fully generic and needs to be customized as per your environment. Basically depends upon the job name.

Query to Rename SQL Agent Jobs having particular naming conventions:

The below example shows job names having names like ‘1_DBA job Name’, ‘2_DBA job Name’, to ‘9_DBA job Name’. Please make the necessary changes as per your environment as provided in the comments.

Script to rename SQL Agent Jobs

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE '%_DBA%'  --Change the LIKE operator
   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''OLD_' +@jobName +''''
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1
OutPut will produce set of statements:

Rename SQL Agent Jobs

The result set is for you to verify the way you want to rename the jobs. Once verified either you execute the statements  Or you can uncomment the statement EXEC (@SQLStatement) in the above code and execute.

Screenshot showing the renamed jobs:

Query to rename SQL Agent jobs

Rollback query for the above changes:

The same script can be taken as reference to rename SQL Agent Jobs by removing first few characters.


DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
DECLARE @jobName1 SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE 'OLD_%' 
   OPEN c1
   FETCH next FROM c1 INTO @jobName
   IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
   WHILE @@fetch_status=0
   BEGIN
       
       SELECT @jobName1 = SUBSTRING(@jobName,5,LEN(@jobName)) --Second parameter may need change as per the job name
       SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''' +@jobName1 +''''
       --PRINT(@SQLStatement)
       EXEC (@SQLStatement)
           FETCH NEXT FROM c1 INTO @jobName
   END
   CLOSE c1
   DEALLOCATE c1

Hope this is helpful. Furthermore you are most welcome to tweak the queries as per your requirement. I am open to feedback therefore please feel free to comment.
In addition you may also like the below queries:

List all permissions for a user in all or selective databases
Script to List All Users with db_owner role in SQL 2000
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/rename-sqlagnetjobs/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Synchronous Database Mirroring Misconceptions

This article is about the Synchronous Database Mirroring misconceptions. Synchronous is one of the operating modes of SQL Database Mirroring also known as High Safety mode; High Safety of transactions in case of failover.

Let’s see what Microsoft TechNet says:

When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.
After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction’s log to disk. Note the wait for this message increases the latency of the transaction.

The common misconceptions/confusions are as follows:
  1. Principal server first commits the transaction and then sends log records to the mirror and wait until mirror server writes the same to the database.
  2. Principal sends its log to the mirror and once it commits in mirror then only it commits in principal.

If you know what happens during commit, it is a bit easier. Commit does not mean the transaction is written to the database (data file). When you commit a transaction SQL Server writes it to the log file on the disk, in other words it hardens the log. This confirms the durability of the transaction.

Let’s take an example of the following transaction:

Begin Tran

Statement 1

Statement 2

Commit;

SQL Server writes the log records in the log file of principal database as per normal process of logging. Mirror server follow the same.

When the application fires a commit statement, Synchronous Database Mirroring mechanism has to confirm, mirror server has written all the log records related to the transaction before sending confirmation to the application.

So, from above example transaction, It is possible that SQL Server has already written the Begin Tran and Statement 1  to the log in principal and mirror server.

The following diagram depicts the same:

Synchronous Database Mirroring

Now lets see what happens when application fires a Commit Tran:
    1. Principal server receives the commit transaction from client.
    2. The principal server writes the log records for the transaction to the log file and sends the log records to the mirror server at the same time as shown below.Synchronous Database MirrorringNote: Principal Server has written the log records to the log file. So one of the misconception is cleared here. Principal Server does not wait for acknowledgement from mirror to commit the transaction.
    3. Principal server completes the I/O  but it cannot send the confirmation to the client at this point.
    4. The mirror server hardens the log in disk.
    5. I/O on the mirror server completes.
    6. Returns an acknowledgement to the principal server.
    7. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation/Acknowledgement to the client.

The following diagram shows the complete cycle:

Synchronous Database Mirroring

Note: The misconception, Principal Server waits until Mirror Server writes the transactions to the mirror database is not true. Synchronous database mirroring confirms that the Mirror writes the log records  to the log file and not data file, before sending acknowledgement, hence even if the principal crashes after that, all the log records of the committed transactions are durable and mirror can take over the role of principal. And that is why it is also know as High Safety Mode.

The mirror server continuously writes data to mirror database from redo queue. In Asynchronous mirroring the Principal Server does not wait for the Acknowledgment from the mirror.

I hope this clears few misconceptions or confusions of Synchronous database mirroring. Please let me know if you have any questions.

You may like the following articles as well:

Myth around Remote Query Timeout Option
Remove or Delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)
SSRS Encryption Key Backup Information
SQL Database Restore using NetBackup

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/synchronous_mirroring/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Issues with Uninstall OLE DB Provider for DB2

After reading this article you’ll be able to fix if standard method of uninstall OLE DB Provider for DB2 does not work. The requirement was to upgrade OLE DB provider for DB2 from version 3.0 to version 5.0. As per Microsoft Document you cannot upgrade OLE DB Provider for DB2 without uninstall of prior versions.

The following instructions are listed in Microsoft Document named “Microsoft OLE DB Provider for DB2 Version 5.0

Upgrade from Previous Version

Microsoft OLE DB Provider for DB2 V 5.0 does not upgrade previous releases. If you have the following previous versions installed, then you must remove them prior to installing the Microsoft OLE DB Provider for DB2 V 5.0.”

To uninstall the product

You can use Windows Programs and Features to remove the product.

  1. Click Control Panel, click Programs, and then click Programs and Features. The Uninstall or change a program dialog appears.
  2. In the Name list, double click Microsoft OLE DB Provider for DB2 Version 5.0. The Data Provider Installation Wizard appears.
  3. Click Next to get started.
  4. On the Program Maintenance dialog, click Remove.
  5. On the Remove the Program dialog, click Remove.
  6. When prompted by Windows User Account Control, click Yes.
  7. On the Completion page, click Finish

This works in ideal condition. Uninstall of OLE DB Provider for DB2 does not always go well and throw weird error messages. The Uninstall may fail due to different reasons like existing DB2 version was not properly installed, someone deleted some of the MSI files or the registry corruption etc.

One of the error message looks like as follows:

MSI (s) (78:64) [21:44:40:923]: Note: 1: 1725
MSI (s) (78:64) [21:44:40:923]: Product: Microsoft OLE DB Provider for DB2 -- Removal failed.
MSI (s) (78:64) [21:44:40:923]: Windows Installer removed the product; Product Name: Microsoft OLE DB Provider for DB2 Product Version: 8.0.4294.0. Product Language: 1033. Manufacturer:Microsoft.Removal success or error status: 1603

As I mentioned earlier the error messages are strange and generic and googling did not help to resolve this particular issue. After this article it may help though 😉
In our case what worked was voiding the existing version of DB2 by changing the registry settings.
Please be careful and make sure to take a backup of your registry settings before following the below mentioned steps.

Steps to Rename The Registry Settings For Existing Version of DB2:
  • Go to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Sna Server and rename “Sna Server” to “_Sna Server” as shown in the following screen shot:

Uninstall OLE DB Provider for DB2

Similarly make the following changes:

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\_Host Integration Server
  • HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Sna Server
  • HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Host Integration Server
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\_00FAC227E6ED834428ED6E468B6B22B2
  • HKEY_CLASSES_ROOT\Installer\Products\ _00FAC227E6ED834428ED6E468B6B22B2
  • Than go to the Program Files Directory and for the Microsoft OLEDB Driver for DB2 and just put an (Underscore) in front of the directory name.

You may be thinking why only (Underscore), you can put anything meaningful like “DB2V3_Old”. It’ll work, but if you put symbol it’ll appear at the top and easy to identify what you have changed or for future reference.

I am leaving that decision up to you. The below screen shot shows how it looks in the registry after renaming:

Uninstall OLE DB Provider for DB2

Once you are done with all the changes your new install should work.

Programs and features looks like both the versions stays happily:

Uninstall OLE DB provider for DB2

You also like to know How to Download OLE DB Provider for DB2

SQL Database Restore using NetBackup
SSRS Encryption Key Backup Information
Remove or Delete Tempdb data file without restarting SQL Server
How to Fix if All logins get deleted due to Trigger Execution
Myth around Remote Query Timeout Option

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/uninstall_db2_provider/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

How to Download OLE DB Provider for DB2

This article is about how to download OLE DB Provider for DB2. This is a Microsoft Data Provider which offers tools and technologies using which SQL Server can access DB2 Databases.

The requirement was just to install DB2 driver and so change time was kept minimal. But I had to spend more than an hour just to find the required file in Microsoft Site. So this five minutes read can save your lot of time.

You may give it a try to search it yourself before reading this article and will get you know why simple things are not always easy.

If you are in the middle of change like me and your search engine lands you here then you can directly go to the end of this article where I have provided direct links to download. Later you can come back and continue to read.

The following instructions are listed in Microsoft Document. This example shows the download of OLE DB Provider for DB2 Version 5.

To Install the Product:
  1. Go to the Microsoft Download Center.
  2. Download either the x86 (32-bit) or the x64 (64-bit) version of DB2OLEDB5_x64.msi installation program.
  3. Double-click the .msi file to start the Installation Wizard
  4. Click Next to get started.
  5. License Agreement page, review the license terms, click the I accept the terms in the license agreement option, and then click Next.
  6. Registration Information page, enter your Name and Company, and then click Next.
  7. Feature Selection page, optionally click Browse to change the Folder name in which to install the product, optionally click Disk Cost to space required to install the product, and then click Next.
  8. Ready to Install the Program page, click Install.
  9. When prompted by Windows User Account Control, click Yes.
  10. On the Installing page, view the status of the installation process.
  11. On the Completion page, click Finish.

Install looks like a cakewalk until you pass the very 1st step.

Once you go to Microsoft Download Center:

Download OLE DB Provider for DB2

Wow! Now what? Which option to choose or what should you search for?

Right here I wasted a lot of time. What you should search for is Feature Packs of SQL Server Version for which you want to install the OLE DB provider for DB2.

Download OLEDB Provider For DB2 Microsoft Download Center

Choose the link showing Feature Pack as shown below:

OLEDB Provider for DB2 Microsoft Download Center

You will get the download option as shown:

OLEDB Provider for DB2 Microsoft Download Center

Click on Download and you get the options of DB2OLEDB5_x64.msi file to choose from:

Download OLE DB Provider for DB2

Select the checkbox for DB2 and click on Next and the download process should start.

For SQL Server 2012 and prior versions you need to expand Install Instructions:

Download OLE DB Provider for DB2

Then search for DB2 keyword and you will get the option to download:

Download OLE DB2 Provider for DB2

Hope this is useful and please let me know by your valuable comments.

Direct links  to Download OLE DB provider for DB2:

SQL Server 2016 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

SQL Server 2014 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2012 Feature Pack:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065

SQL Server 2008 R2 Feature Pack

https://www.microsoft.com/en-us/download/confirmation.aspx?id=16978

You may also like below articles:
SQL Database Restore Using NetBackup
SSRS Encryption Key Backup Information
Myth around Remote Query Timeout Option
User Defined Profiler Template Location
Fact about Dedicated Administrator Connection (DAC)

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/oledb-db2/
Twitter
PINTEREST
LinkedIn

{ 2 Comments }

SQL Database Restore using NetBackup

In this article I am going to explain how to perform Database restore using NetBackup tool. I was fortunate enough to learn from experienced people, otherwise it was difficult to follow the materials provided. In this post I have tried to explain the database restore process in detail.

Launching NetBackup Tool and connecting to SQL Instance:
  • Click on Start > All Programs > Symantec NetBackup > NetBackup Agents > Right Click on NetBackup MS SQL Client > Run as Administrator as shown in the following example:

Note: Run as Administrator is a very important step, otherwise the tool won’t connect to SQL Instance and throws ambiguous error messages.

Image showing lunching NetBackup using Run As Admin option

  • In the User Account Control Window click on Yes.

Image of User Account Control Wizard

  • In the NetBackup Database Extension Window click on Ok to select the connection properties.

Image of NetBackup Database Extension Wizard to choose the connection properties

Now let’s see the ways to connect default and named SQL instances through NetBackup tool.

To Connect Default Instance:
  • In the SQL Server Connection properties window put the host name.
  • For Instance field, leave it as default click on Apply and then Close.

Image showing connection properties for default instance

Note: You do not have to put any credentials for UserId field. Leave it as default. SQL Server version, Security and Host Type will be populated once it is connected as shown in the above screen shot.

To Connect Named SQL Instance:
  • If the SQL Instance name is HostName\SQLInstance, you need to put the host and Instance name as shown in the following screen shot.
  • Click on Apply and then close.

Image about NetBackup connection to Named Instance

Now you are connected to the target Instance. You can always verify if you are connected to the correct instance from File > Set SQL Server Connection properties.

Database Restore Steps using NetBackup:

Go to File > Restore SQL Server Objects

Image showing NetBackup Restore step

Provide the SQL Server Instance name from which backup has to be restored, i.e. Source SQL Server name.

Image showing NetBackup history

You can use time filter to view the backups in particular date range.

Note: Unlike native backup of SQL Server, NetBackup tool does not create any .bak files which can be copied from source to destination. The backup files/images are scattered on storage. This is the reason you have to provide Source and Destination Server name during the restore process.

Click on Ok to go the next wizard “Restore Microsoft SQL Server Objects”.

In the below screen shot I have shown full and differential backup images. I am going to show you the steps for restore using full backup image.

For Scripting choose “Create a move template”. This will create a restore template (.bch file) and the Save radio button will get selected by itself.

NetBackup object wizard

Click on Restore.
Provide a meaningful filename for the .bch file.

NetBackup restore shwoing .bch file

Click on Save.
You’ll get a pop up message asking “Would you like to open it in notepad?”
Click on Yes.

NetBackup restore showing pop up message

In the below screen shot I have explained what all places you have to make the changes. This is very important and you have to pay attention:

Template/script showing different places to modify:
NetBacup restore showing places to change file names

Note: If you choose differential backup image to restore, the tool will automatically choose the base full database backup image. So in that case you have to make the same changes for all the data and log file locations.

And same goes with log file restore as well. Once you are done with all the changes save the file.
Now go to File > Manage Script Files:

NetBackup Restore showing Mange Script Files option

Select the created template. e.g. “DatabaseName_11072017” as shown in the below screen shot.
Click on Start.

It will ask for confirmation. Click on Yes.

NetBackup Restore confirmation message

Another self-explanatory message will pop up.
Clock on Ok.
NetBackup Restore step

It will take you back to the “Manage Script” wizard.
This is a bit confusing or rather a bug. Just click on Cancel.
NetBackup Restore tool bug

Your restore process should start and can be viewed the progress in File > View Status.

NetBackup Restore view status

Hope this is helpful.
You may like the below articles as well:

Myth around Remote Query Timeout Option
SSRS Encryption key backup information
Fact about Dedicated Administrator Connection (DAC)

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/netbackuprestore/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

List of Users with db_owner Role in All Databases

Just after returning from Christmas holiday, I got a requirement to send a report having a list of users with db_owner role in all databases in SQL Server Instance. There was an internal audit going on and the auditor wanted that report.
I knew my dear friend “Internet” will help me get that report in lieu of few clicks. I really didn’t want to spend time on writing a whole new script as I was still in holiday mood.

Wait a sec! I think I already had something which I could modify a bit to get the work done. What I had was List all permissions for a user in all or selective databases

But the output is something which I cannot send a report since it shows blank rows for the databases on which the user does not have access to as shown below:
Image of query output showing some blank rows

So, I used a temporary table to get the result I wanted. In fact I have added the same in the above article as well upon realization of the problem.

There are two ways you can pull the same report.

  • Using Cursor to scan through all the databases.
  • Using the undocumented stored procedure sp_MSforeachdb.
Using Cursor to retrieve a List of users with db_owner role in all databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE 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 pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

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

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

Sample output:

Image showing output of all users with db_owner role in all databases using cursor

Using sp_MSforeachdb to retrieve the same information:
CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_msforeachdb '

use [?]

BEGIN

  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 pp.name=''db_owner'' and p.name<>''dbo'' 

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission
Sample output is same as above:
Image showing the sample out of sp_MSforeachdb
Query to Retrieve List of all users having db_owner role in Selective databases:
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('master','ReportServerSSRS','ReportServerSSRSTempDB')
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 pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

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

SELECT * FROM #UserPermission

DROP TABLE #UserPermission
Sample output of selective databases:
Sample Output for selective databases
Using sp_MSforeachdb to list users with db_owner role only in user databases:
CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_msforeachdb '

use [?]

IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN

  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 pp.name=''db_owner'' and p.name<>''dbo''   

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission
Sample result set:

Image showing output of sp_MSforeach db for user databases

Hope this helps. If you have any suggestion please feel free to put in comments section.

You may also find the following scripts useful for your day to day life:
Script to find the SQL Agent Job Name in Execution
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

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/users-db_owner-access-databases/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

SSRS Encryption Key Backup Information

Today I am going to discuss about an uncommon requirement and that is to find out SSRS Encryption Key backup information. In other words if there is any backup taken for the key and if yes then what is the location and backup date.

Now the question is very legitimate as they wanted to know if the reports can be restored in case situation demands. Not being a BI expert started my research from scratch.

Tried to query the “Keys” table in ReportServer database:

Image showing the keys table outputAs you can see no information about location of keys or at least last backup information.

A wild guess to check “backup_devices” view but no luck as expected:

Image showing output of backup_device view
Could not find any information in registry keys as well.
As per discussion with my colleague I installed SSRS on my personal laptop and then started a Process Monitor trace right before backing up the encryption key. During the process, a RSA machine key is created in C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\.

This was like the light at the end of the tunnel and decided to dig further to discover something which would make me feel like a champion 😉

The below screen shot shows the generated machine key and path:

Image showing the machine key path

Initiated Encryption Key backup and checked in the process monitor:

Image showing the key backup path in process monitorI could see that the path showing the key backup as shown in the above screen shot.

Next tried to search if any information regarding the machine key has been logged:

That is searching for the path C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\
Image of process monitor which does not have the Machine key creation information This “Cannot find string” message shattered my hope 🙁

To understand how the machine key got generated, I uninstalled SSRS and checked the same path and the key was still lying there. Did a reboot of the system but it was still there.
I manually deleted the key, reinstalled and configured SSRS and the key got created. So the machine key gets generated while configuring SSRS.
So my assumption:

Machine key gets generated while taking SSRS Encryption Key Backup was wrong.

Conclusion:

I have checked with many experts in different forums and the most fruitful one was https://www.sqlservercentral.com.
What we can conclude from all these is as of now SQL Server does not record the information of SSRS encryption key backup path. It make sense because the .SNK file could be moved to another location manually.
But I expect Microsoft to at least record the information about the successful backup just the way they record database backup information.

Work Around:

The only work around is to have a company policy of some sort on where to backup these keys. Like you may want to suggest to have a policy/documentation to do the backup every x months on x path.
Or use default location for the backup of the instance there are some things you could do using the registry.
And then use PowerShell to find the LastWriteTime of the .SNK file.

Here I have explained every way I tried and failed to find the required information. Sometimes it is important to know what all ways does not work so that you can think in other direction.
Well if you find the solution please let me know and that is why I request you to share this with your friends/network using the below social share icons.

You may also like to know about:
Myth around Remote Query Timeout Option
Delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/ssrs-encryption-key-backup-information/
Twitter
PINTEREST
LinkedIn

{ 2 Comments }

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

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/script-to-find-the-sql-agent-job-name-in-execution/
Twitter
PINTEREST
LinkedIn

{ 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

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/list-all-permissions-for-a-user-in-all-or-selective-databases/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }