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

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s