AWS-RDS :- Backing up SQL Database

Background

Backing up and restoring databases is one of the common tasks performed by DBAs.

Because of the regularity of the tasks, the DBA often schedules them to periodically occur and walks away.

Outside of occasionally alerts due to storage issues, life is good and fancy free.

 

AWS/RDS

Snapshots

In the Amazon RDS World, snapshots can be configured to auto-occur and life remains drama free.

 

S3 Storage

To facilitate husbandry of databases in-house and general safe-keeping, AWS now supports actual database backups.

Backup

Outline

The steps are:

  1. S3
    • Provision S3
  2. AWS Identity and Access Management IAM Role
    • Setup IAM Role
      • Setup Trust Policy for Native Backup and Restore
      • Setup Permissions Policy for Native Backup and Restore
  3. RDS Instance
    • RDS Instance Option Group
      • Option Groups
        • SQLSERVER_BACKUP_RESTORE

 

Steps

Step – S3

For the sake of brevity we will not touch upon provisioning a new S3.

We will instead focus on switching roles to a predefined role that has access to the S3 bucket.

Need to know
  1. S3 bucket
    • It is customal to have various S3 buckets in an organization
    • Determine the one pre-assigned to the group
    • Understand the folder structure
Permissions

In our original setup, my personal account did not have permission to the S3 bucket.

Permissions was granted to specific roles, and not to individual user’s personal accounts.

And, so if at first you fail to see S3 folders or the ones that you are looking for, please do not be too dissuaded,  as permissions might have been granted to specific roles instead.

 

Step – Identity & Access Management ( IAM )

Just as for S3, Identity and Access Management ( IAM ), is another big area and so we will skip the details here.

There are two areas that are important and have to be satisfied to allow RDS to access S3 buckets.  Those two areas are:

  1. Trust Policy
  2. Permission Policy
Trust Policy
Syntax

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Explanation
  1. Designate that principal rds.amazonaws.com is allowed access to the S3 Service

 

Permission Policy
Syntax
{
    "Version": "2012-10-17",
    "Statement":
    [
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
        "Resource": "arn:aws:s3:::bucket_name"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
        "Resource": "arn:aws:s3:::bucket_name/*"
        }
    ]
}


Explanation
  1. Bucket
    • s3:ListBucket
      • List Bucket
    • s3:GetBucketLocation
      • Get S3 Bucket Location
  2. Bucket and sub-folders
    • s3:GetObjectMetaData
      • Get Object Metadata
    • s3:GetObject
      • Get Object
    • s3:PutObject
      • Put Object
    • s3:ListMultipartUploadParts
      • List Multipart upload parts
    • s3:AbortMultipartUpload
      • Abort Multipart Upload

Step – RDS Instance Option Group – Option Group – SQLSERVER_BACKUP_RESTORE

As said earlier, the option to perform database backup and restore is new to RDS.

The specific option’s name is SQLSERVER_BACKUP_RESTORE.

Need to know
  1. Review the SQL Server Instance
    • Review the Option group
      • Console
        • Sample
          • Region :- US-East-2
            • https://us-east-2.console.aws.amazon.com/rds/home?region=us-east-2#option-groups:

 

Step – Backup

After all that, we are finally at the point where we can backup the database

Metadata

The Stored Procedure that we will using is dbo.rds_backup_database.

Let us review how it should be invoked.

To do so we will use the sp_help command.

Syntax

exec msdb.dbo.sp_help 'dbo.rds_backup_database'

Output

Actual
Syntax

exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
        , @overwrite_S3_backup_file=@overwrite

Sample

declare @dbName sysname
declare @s3arn  sysname
declare @overwriteS3BackupFile int

set @dbName = 'datahub';
set @s3arn = 'arn:aws:s3:::sqlServer/backup/datahub/datahub_20171207_0448PM.bak'
set @overwriteS3BackupFile = 1

-- exec  msdb.dbo.sp_help 'rds_backup_database'
exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
	, @overwrite_S3_backup_file = @overwriteS3BackupFile



Output

Review
Syntax

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName

Sample

declare @dbName sysname

set @dbName = 'datahub';

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName


 

Output

 

References

  1. Amazon / AWS /RDS
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Importing and Exporting SQL Server Databases
      • Importing and Exporting SQL Server Databases
        Link
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Options for the Microsoft SQL Server Database Engine
      • Options for the Microsoft SQL Server Database Engine
        Link

Amazon AWS/SQL Server – Task Status

Background

Back in 2012, Amazon started offering support for MS SQL Server.

Recently they added support for whole database backup and restore.

 

Review

To review the status of current operation and past ones, one can issue a query against the msdb.dbo.rds_task_status Stored Procedure.

 

Metadata

msdb.dbo.rds_task_status

Syntax


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		, @task_id = @taskID

Sample

Sample  – Review all tasks

SQL


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 

Output

Grid

Sample  – Review tasks targeting specific database

Sample  – Review tasks targeting specific database ( datahub )


declare @dbname  sysname
declare @taskID  int

set @dbname = 'datahub'

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		--, @task_id = @taskID

Output

Sample  – Review tasks targeting specific task

Sample  – Review tasks targeting specific database ( task )

If you initiated a task and recorded the task Number, you can go back and inquire about that task, as well.

Sample  – Review tasks targeting specific database ( task – Not Indicated)

If you indicate that you will be passing a task ID, but pass in a null one, you will get an error.


declare @dbname  sysname
declare @taskID  int

--set @taskID = 

exec msdb.dbo.rds_task_status 
     @task_id = @taskID

Output

Grid

Textual

Msg 50000, Level 16, State 1, Procedure rds_task_status, Line 36 [Batch Start Line 2]
Could not find the specified task. Execute without any parameters to show all tasks.

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