SQL Server Backup & Performance Counters

Background

Experiencing  longer than expected Production to Development environment backup and restore cycles.

Wanted to capture performance metrics.

Outline

Here are metrics we will consider:

  1. Ongoing Monitoring
    • MS Windows Performance Monitor ( perfmon )
  2. Summary
    • SQL Server Statement

Performance Monitor ( perfmon )

Metrics

Items

  1. SQL Server / Backup Device
    • Device Throughput Bytes/sec
  2. SQL Server, Databases
    • Backup/Restore Throughput/sec

Tabulate

Object Counter
SQL Server, Backup Device Object
Device Throughput Bytes/sec Throughput of read and write operations (in bytes per second) for a backup device used when backing up or restoring databases. This counter exists only while the backup or restore operation is executing.
SQL Server, Databases Object
Backup/Restore Throughput/sec Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.

Select Metrics

Selections

Selection – SQLServer:Databases

performanceCounter.choose.SQLServer.Databases.20180914.1202PM.PNG

Selection – SQLServer:Backup Device

performanceCounter.choose.backupDevice.20180914.1045AM.PNG

Captured Metrics

Captured Metrics

Image – 01

performanceCounter.20180914.1137AM.PNG

Image – 02

performanceCounter.20180914.1130AM.PNG

Image – 03

performanceCounter.20180914.1128AM.PNG

Backup Statement Output

Metrics

Output #1

backupDB.20180914.1215PM

 

Summary

  1. Physical Disk/Disk Writes/sec divided ( / ) by Physical Disk/Avg. Disk Bytes/sec
    • Disk Writes/sec
  2. SQLServer:databases Backup/Resource throughput/sec Versus Physical Disk/Disk Writes/sec
    • Likely SQLServer:databases Backup/resource Throughput/sec will be a multiplier of the Physical Disk/Disk Writes/sec.

 

SQL Server – Database Restore Steps thru lenses of errorlog

Introduction

Wanted to document database restore steps as seen from trail left in SQL Server Instance’s errorlog file.

Restore

Restore – Full Database

Step Sub Task Logged Explanation

Start Database (Database started in boot-strap mode )
Starting up database ‘csAnalytics’.

Recovery or NO Recovery
NO Recovery
The database ‘csAnalytics’ is marked RESTORING and is in a state that does not allow recovery to be run. Indicate that we are restoring and will not proceed to actual database recovery; as we would have done during normal SQL Server Instance start.
Recovery
Recovery is writing a checkpoint in database ‘csAnalytics’ (9). This is an informational message only. No user action is required. Database restore wrote a checkpoint ledger to indicate progress.
2018-03-20 05:46:51.110    spid70    Recovery completed for database rbpivr1 (database ID 42) in 12 second(s) (analysis 5444 ms, redo 20 ms, undo 5171 ms.) This is an informational message only. No user action is required. Informational in terms of duration.

Here is what is logged – Database name, Assigned Database ID, and total time.

Time breakdown – Analysis, redo, undo.

2018-03-20 05:46:51.550    spid70    Filegroup fgLOB in database rbpivr1 is unavailable because it is Offline. Restore or alter the filegroup to be available. In the case of partial restores, the file groups implicitly excluded.
2018-03-20 11:10:58.790    Backup    Database was restored: Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.bak’}). Informational message. No user action required. Informational in terms of statistics.

Data logged includes name given to target database, creation time, LSN, number of dump/backup devices, and backup device names.


In-Memory Database ( Hekathon )
[INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [42]. Starting offline checkpoint worker thread on a hidden SOS scheduler. Hekathon Offline Checkpoint
Check Database
CHECKDB for database ‘csAnalytics’ finished without errors on 2011-08-03 00:06:43.420 (local time). This is an informational message only; no user action is required. Minimal Database Consistency ran.
Resource DB Alignment
2018-03-20 05:46:51.950    spid70    Synchronize Database ‘rbpivr1’ (42) with Resource Database. Database system objects aligned with Instance’s Resource DB.

Especially important when database backup file restored from earlier SQL Server version.


Restore Complete
Restore is complete on database ‘rbpivr1’.  The database is now available. Restore Completed

Database Setting
Setting database option MULTI_USER to ON for database csAnalytics. Database transitioned from Single to Multi User

 

Restore – Transaction Log

Logged Explanation
Log was restored. Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:422:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.trn’}). This is an informational message. No user action is required. Conveys that Transaction Log backup file was applied to specified database.  It lets us in on the starting and ending LSN.  And, the name of the transaction backup file.

Summary

Data logged in the SQL Server Instance log is quite useful in gleaning what is occurring behind the scene when a database is restored.

Comparing logs from different SQL Server Instances and Database setup affords familiarity with how various database setup and restore options is considered and acted upon by the DB restore facility.

As summary, the code will consider:

  1. Will the database be recovered or left in no_recovery
    • No recovery basically means subsequent transaction logs will be applied
  2. Fast Database Consistency check occurs
  3. Redo and Undo Log Processing
    • Logs contain both redo and undo portions
      • Redo
        • Once database is fully restored, committed transactions that are still in the transaction log, but have yet to be checkpoint into the database data files are applied to restored database datafiles
      • Undo
        • Undo means that data is not committed and still in flight.
        • The data can later be committed ( commit tran ) or discarded ( rollback )
  4. Resource DB Alignment
    • When a database from a previous version of SQL Server is restored unto a newer version the restored db system dictionary has to be brought up to date
  5. In Memory Database
    • In-Memory tables and programmable objects are read from database files into memory

SQL Server – Review Database Restore Timeline

Background

Reviewing Database Restore timeline and wasted to share what we have thus far.

 

Metadata

SQL

/*

    a) restorehistory (Transact-SQL)
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql

*/
; with cteRestoreHistory
(
      [restoreHistoryID]
    , [databaseName]
    , [restorer]
    , [restoreDate]
    , [restoreStopAt]
    , [restoreType]
    , [backupSetID] 
    , [sequenceNbr]
)
as
(

    select 
              [restoreHistoryID]
                = tblRH.[restore_history_id]

            , [databaseName]
                = tblRH.[destination_database_name]

            , [restorer]
                = tblRH.[user_name]

            , [restoreDate]
                = tblRH.[restore_date]

            , [restoreStopAt]
                = tblRH.[stop_at]

            , [restoreType]
                = tblRH.restore_type

            , [backupSetID] 
                = tblRH.[backup_set_id] 

            , [sequenceNbr]
                = DENSE_RANK() 
                    OVER 
                    (

                        PARTITION BY 
                            tblRH.[destination_database_name]

                        ORDER BY 
                            tblRH.[restore_date] DESC
                    )

    from   msdb.[dbo].[restorehistory] tblRH


)

, cteRestoreType
(
      [restoreType]
    , [restoreTypeLiteral]
)
as
(
    select 
              'D'
            , 'Database'
    union
    select 
              'F'
            , 'File'
    union
    select 
              'G'
            , 'FileGroup'
    union
    select 
              'L'
            , 'Log'

)
select 
          [database] 
            = tblSD.[name]

        , [dbid]
             = tblSD.[database_id]

        , [restoreInitatedBy]
             = tblRH.[restorer]

        , [restoreDate]

        , [restoreType]
             = tblRH.[restoreType]

        , [restoreTypeDescription]
            = cteRT.restoreTypeLiteral

        , [backupMachineName]
            = tblBS.[machine_name]

        , [backupInitiatedBy]
            = tblBS.[user_name]

        , [databaseVersion]
            = tblBS.database_version

        , [backupFile]
            = tblBMF.physical_device_name

        , [originalBackupStartDate]
            = tblBS.backup_start_date

        , [originalBackupCompletionDate]
            = tblBS.backup_finish_date

        , [originalBackupDuration (in minutes)]
            = datediff
                (   
                      minute
                    , tblBS.backup_start_date
                    , tblBS.backup_finish_date
                )

from   sys.databases tblSD

inner join cteRestoreHistory tblRH

        on tblSD.[name] = tblRH.[databaseName]

inner join msdb..backupset tblBS

    ON tblRH.[backupSetID] = [tblBS].[backup_set_id]

INNER JOIN msdb..backupmediafamily tblBMF
    ON [tblBS].[media_set_id] = [tblBMF].[media_set_id] 

left outer join cteRestoreType cteRT
    on tblRH.[restoreType] = cteRT.restoreType


where tblRH.sequenceNbr = 1

order by

          tblSD.[name]
        , tblRH.[restoreDate] desc


Output

Sample

SQL Server – Backup – When offline filegroups exist

Background

A few months we performed a partial restore.

When we tried to backup the partially restored database we ran into potholes.

Issues

Sample SQL and Error Messages

  1. SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’ TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
    • Error Message :- “Msg 3004, Level 16, State 1, Line 1
      The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.”
    • Corrective Measures
      • SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’, READ_WRITE_FILEGROUPS TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
  2. SQL :- “backup database [QA_rbpivr1] TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’
    • Error Message :- Msg 3007, Level 16, State 1, Line 1
      The backup of the file or filegroup “rbpivrLOB_01” is not permitted because it is not online. Container state: “Vacant” (6). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.”
    • Corrective Measures
      • SQL :- “backup database [QA_rbpivr1] FILEGROUP =’PRIMARY’, FILEGROUP =’DATA’, FILEGROUP =’INDEX’, FILEGROUP =’fg_rbpivr1_memory’
        TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’ WITH INIT, FORMAT , STATS=1″

 

Remediation

To backup we have to explicitly specify each filegroup we will like backed up.

And, in doing so, omit the filegroups that are offline.

 

Code

Sample


backup database [QA_rbpivr1]    
FILEGROUP ='PRIMARY'
    , FILEGROUP ='DATA'
    , FILEGROUP ='INDEX'
    , FILEGROUP ='fg_rbpivr1_memory'  
 TO DISK  = 'Z:\SQLBackups\QA_rbpivr1.bak' 
 WITH INIT, FORMAT  , STATS=1

Stored Procedure

 


use [master]
go

if object_id('[dbo].[sp_DBBackupCustomized]') is null
begin

    exec('create procedure [dbo].[sp_DBBackupCustomized] as ')

end
go

alter procedure [dbo].[sp_DBBackupCustomized]
(
      @dbname			sysname
    , @filenameBackup	sysname = null --= 'NUL'
    , @stat				smallint = 1
    , @scriptOnly		bit = 0
    , @debugGUI			bit = 0
)
as
begin

    set nocount on;

    declare @dbid int
    declare @tblFile TABLE
    (

          [id]			int not null identity(1,1)
        , [name]		sysname not null

        , [dataSpaceID] int null

        , [filegroup]	sysname null
                            default ''

        , [type]		int not null
        , [type_desc]	sysname not null

        , [state]		int not null
        , [state_desc]	sysname not null

        , [include]	as 
                        case
                            when (
                                        ( [type] in (0, 2) )
                                    and ( [state] = 0) 
                                 )
                                    then 1
                            else 0
                        end
    )

    declare @tblFileFromDB TABLE
    (
          [id]			int not null identity(1,1)	
        , [name]		sysname
        , [fileid]		int
        , [filename]	sysname 
        , [filegroup]	sysname null
        , [size]		sysname
        , [maxsize]		sysname
        , [growth]		sysname
        , [usage]		sysname
    )

    declare @tblFileGroupFromDB TABLE
    (
          [id]				int not null identity(1,1)
        , [name]			sysname
        , [dataSpaceID]		int
        , [type]			sysname
        , [type_desc]		sysname
    )

    declare @tblFileGroupIncluded TABLE
    (
          [id]		  int not null identity(1,1)
        , [filegroup] sysname not null
    )

    declare @id int
    declare @idMax int

    declare  @cmd nvarchar(255)

    declare  @FORMAT_FILEGROUP_QUERY		nvarchar(4000);
    declare  @FORMAT_BACKUP_CMD			nvarchar(4000);
    declare  @cmdBackup			nvarchar(4000);
    declare  @statAsVarchar     varchar(10)

    declare @fileGroupName		nvarchar(200)
    declare @fileGroupSingle	nvarchar(200)
    declare @fileGroupBuffer	nvarchar(4000)

    declare @fileGroupBufferNotEmpty bit

    declare @CHAR_NEWLINE		varchar(10)
    declare @CHAR_QUOTES_SINGLE	varchar(10)
    declare @CHAR_COMMA			varchar(10)

    DECLARE @pathBackupDefault NVARCHAR(4000) 

    set @CHAR_NEWLINE = char(13) + char(10)
    set @CHAR_QUOTES_SINGLE = ''''
    set @CHAR_COMMA	  = ' , '
        
    set @fileGroupBufferNotEmpty = 0

    set @FORMAT_BACKUP_CMD=
            'backup database [%s] '
                + ' %s ' -- FILEGROUP
                + ' TO DISK '
                + ' = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_NEWLINE
                + ' WITH INIT, FORMAT '
                + ' , STATS=%s'
                
    set @FORMAT_FILEGROUP_QUERY
            = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    set @dbid = db_id(@dbname);

    if (@filenameBackup is null)
    begin

       EXEC master.dbo.xp_instance_regread 
                      N'HKEY_LOCAL_MACHINE'
                    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                    , N'BackupDirectory'
                    , @pathBackupDefault OUTPUT
                    , 'no_output' 

        set @filenameBackup = @pathBackupDefault + '\' + @dbname + '.bak'

    end

    insert into @tblFile
    (

          [name]
        , [dataSpaceID]

        , [type]
        , [type_desc]

        , [state]
        , [state_desc]

    )
    select 
              tblSMF.[name]
            , tblSMF.[data_space_id]

            , tblSMF.[type]
            , tblSMF.[type_desc]

            , tblSMF.[state]	
            , tblSMF.[state_desc]

    from   sys.master_files tblSMF

    where  (

                    --( tblSMF.[state] = 0 )
                    ( tblSMF.[database_id] = @dbid )

           )	

    --has_dbaccess(@dbname)
    --print N' '
    --select @cmd = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    exec master.sys.xp_sprintf
              @cmd output
            , @FORMAT_FILEGROUP_QUERY
            , @dbname

    insert into @tblFileGroupFromDB
    (
          [name]		
        , [dataSpaceID]		
        , [type]	
        , [type_desc]	
    )
    exec (@cmd)

    update tblF
    
    set [filegroup] = isNull(tblFGFDB.[name], '')

    from   @tblFile tblF

    inner join @tblFileGroupFromDB tblFGFDB

            on tblF.[dataSpaceID] = tblFGFDB.[dataSpaceID]

    if (@debugGUI=1)
    begin

        select 
                 [src] = '@tblFile'
                , tblF.*

        from   @tblFile tblF

    end

    set @statAsVarchar = cast(@stat as varchar(10));

    set @id = 1
    set @idMax = ( select max([id]) from @tblFile)

    set @fileGroupBuffer = '';
    set @fileGroupBufferNotEmpty = 0;

    while (@id <= @idMax)
    begin

        set @fileGroupName = null

        select @fileGroupName = tblF.[filegroup]
        from   @tblFile tblF
        where  tblF.id = @id
        and    tblF.[include] = 1
        and    not exists
                    (
                        select 1
                        from   @tblFileGroupIncluded tblFGI
                        where  tblFGI.[filegroup] = tblF.[filegroup]
                    )

        if (@fileGroupName is not null)
        begin

            set @fileGroupSingle = 'FILEGROUP =' 
                                    + @CHAR_QUOTES_SINGLE 
                                    + @fileGroupName 
                                    + @CHAR_QUOTES_SINGLE;
            
            if  (@fileGroupBufferNotEmpty = 1)
            begin

                set @fileGroupBuffer = isNull(@fileGroupBuffer, '') 
                                            + @CHAR_NEWLINE
                                            + @CHAR_COMMA

            end
            else
            begin

                set @fileGroupBuffer = '  ' 
                                        + isNull(@fileGroupBuffer, '') 
                                        + @CHAR_NEWLINE

            end

            set @fileGroupBuffer = @fileGroupBuffer + @fileGroupSingle;

            set @fileGroupBufferNotEmpty = 1

            insert @tblFileGroupIncluded
            (
                [filegroup]
            )
            values
            (
                @fileGroupName
            )

        end

        set @id = @id + 1

    end

    exec master.dbo.xp_sprintf
              @cmdBackup output
            , @FORMAT_BACKUP_CMD
            , @dbname
            , @fileGroupBuffer
            , @filenameBackup
            , @statAsVarchar

    print '@cmdBackup ' + @CHAR_NEWLINE + @cmdBackup

    if (@scriptOnly =0)
    begin

        exec(@cmdBackup)

    end

end

go




SQL Server – Database Restore Date

Background

Get a tally of last database restore dates.

 

SQL

Code


select 
		  [database]
			= tblRH.[destination_database_name]

		, [restoreType]
			= case tblRH.[restore_type]
					when 'D' then 'Database'
					when 'L' then 'Transaction'
					else 'N/A'
			  end	

		, [restoreDateAsDate]
			= tblRH.restore_date

		, [restoreDateAsString]
			= convert
				(
					  varchar(30)
					, tblRH.restore_date
					, 100
				)
			 
from   [msdb].[dbo].[restorehistory] tblRH

where tblRH.[restore_history_id]
		=
			(
				select max(tblRH_Inner.[restore_history_id])
				from   [msdb].[dbo].[restorehistory] tblRH_Inner
				where  tblRH.[destination_database_name]
						= tblRH_Inner.[destination_database_name]
				and    tblRH.[restore_type]
						= tblRH_Inner.[restore_type]
			)
		
order by
		tblRH.[destination_database_name] asc



 

Output

SQL Server – Database Restore – Errors – “C:\ClusterStorage\”

Background

Was reviewing SQL Server Agent logs earlier this week and found errors resulting from a database restore SSIS Package.

 

Error

Error Text

The error looks like the one below:


Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Data' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Log' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.


Error Tabulated

Message ID Sample Explanation
 Msg 5133 Directory lookup for the file “C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf” failed with the operating system error 3(The system cannot find the path specified.). Review the designated folder name and ensure it desired.

If so create and/or grant SQL Server Engine permission.

 Msg 3156  File cannot be restored. Use with move to Identify a valid location for the file Please add the move option to the restore command
 Msg 3199  Problems were identified while planning for the RESTORE statement  Flags errored restore operations
 Msg 3013  RESTORE DATABASE is terminating abnormally.  Restore failed abnormally

 

 

 

TroubleShooting

The error is of course the fact the on the originating SQL Instance the database files are located on C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA  ( Data Folder ) and C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data   ( Log Folder ).

 

Remediate

The fix is to create the folders ahead of time

Stay on Originating Folder

Create Folders

exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\'
exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\'

 

Restore Database


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'

 

Use Own Folders

Get File groups from DB Backup File

restore filelistonly
from disk = 'C:\TEMP\IND.bak'</pre>

Create Folders

exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Datafiles\'
exec master.dbo.xp_create_subdir 'F:\Microsoft\SQLServer\Logfiles\'

Use Move Filegroup


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'
with
	  move 'IND_Data' to 'E:\Microsoft\SQLServer\Datafiles\IND_Data.mdf'
	, move 'IND_Log' to  'F:\Microsoft\SQLServer\Logfiles\IND_Log.ldf'
	, replace
	, stats=1

 

SQL Server – Life after Database Restore – Inherited Security

Background

Once a database is restored there a few things one has to do.

With SQL based accounts, one of those things is to realign the database users.

With Windows Authenticated users, things should be OK if in same Windows Domain.

On the other hand, if different domain we have to grant access to targeted Logins, as well.

 

Inherited Security

Let us talk about what I will call “Inherited Security“.

Outline

  1. Restore Database
  2. Create Login
    • Normal login, not sysadmin
  3. Query Login as the non-privilege Login
    • Using “Execute As Login” query one of the tables in the restored database
    • Errored out indicating Login does not have access to restored database
  4. Grant Server role permission to created Account
    • Only applicable if SQL Version is v2014
    • The specific SQL Instance privilege we will like to grant is “Connect any database
    • Again, please keep in mind that “Connect any database” was instituted in v2014
      • Docs / S​QL / T-​SQ​L / statements / GRANT Server Permissions (Transact-SQL)
        Link
  5. On targeted database, allow guest access
    • On targeted database, grant connect access to guest
    • In SQL Server, the guest access is anyone who is registered in SQL Server, but does not have an account on the targeted database
    • Grant specific permissions to guest account

Code

Create Login



--Norton by Symantec // Password Generator
--https://identitysafe.norton.com/password-generator/#
--s19miewRoest

declare @loginName	varchar(30)
declare @password	varchar(30)

set @loginName = 'BISUser'
set @password = 's19miewRoest'

if suser_id(@loginName) is null
begin

	print 'Create Login ' + @loginName + ' ...'

	create login [BISUser]
		with password = 's19miewRoest'
		, check_policy=off
		, check_expiration= off

	print 'Create Login ' + @loginName
end
go

 

Query restored data under guise of normal Login

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

 

Error Image

 

Error Text

Msg 916, Level 14, State 1, Line 9
The server principal "BISUser" is not able to access the database "INDThirdParty" under the current security context.

Remediation – Grant Connect Any database

Code

Grant Connect Any database to Principal
Code

use [master]
GO
GRANT CONNECT ANY DATABASE TO [BISUser]
GO

Retried querying data and got new error message
Error

 

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.

 

Grant permission on targeted database to all users

On targeted database, tried granting appropriate permissions to all users

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'public'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [public]
go

Error

 


Msg 15405, Level 16, State 1, Line 36
Cannot use the special principal 'public'.
Msg 15405, Level 16, State 1, Line 41
Cannot use the special principal 'public'.

Conclusion

So we see that we need more.

 

Remediation – Allow Guest access

Code

Revoke “Connect Any Database” From Principal
Code

use [master]
GO

REVOKE CONNECT ANY DATABASE FROM [BISUser]
GO

On Targeted Database, Enable Guest Access
Code

use [INDThirdParty]
GO

GRANT CONNECT TO [Guest]
GO

Retried querying data and got the permission error message again
Error

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.
Grant permission on targeted database to guest

On targeted database, grant appropriate permissions to guest

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'guest'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [guest]
go

Retry query

On targeted database, grant appropriate permissions to guest

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

SQL Server Profiler

 

Explanation
  1. The NTUserName stays as the logged on user
  2. The LoginName changes as we transition in with “EXECUTE AS LOGIN” and out with “REVERT

Conclusion

We are good!

 

Dedicated

Dedicating to Matt Bowler & Dan,  a commenter on Matt’s Post

Restore database permissions
Posted On: 2013-Jan-10th
Link