By: Varinder Sohal | Updated: 2014-05-30 | Comments (9) | Related: > Contained Databases
Problem
Recently in one of our HR projects we implemented the contained SQL Server database concept. We implemented the policy that going forward we will create Contained SQL Server Users without logins in contained databases which will ease the process of migrating the databases across data centers without worrying about the login scripts. For this project we created two partial contained databases called EmpProfile and EmpCompensation with users ProfileUser (in EmpProfile) and HRinfoUser (in EmpCompensation). Both the users were created using "SQL USER WITH PASSWORD" and were granted read, write and execute privileges. This was sufficient for the HR application to work.
Everything was working fine until the CS Application Team was asked by the HR Management Team to generate ad-hoc reports using both of the HR databases which will provide a combined view of the different databases to management. So the CS Application Team raised a request to the DBA Team to create a new user called CSappsTeam with read and write privileges into both the databases for their team so that they can easily access the objects of both databases and generate the reports. This was a challenge for DBA team as the contained database users cannot read data beyond the database in which they are created.
Solution
Hence to resolve the above mentioned privileges issue we used the following steps as a solution.
Step 1
First we created the databases using the following code
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To Allow Contained databases at server level EXEC sp_configure 'contained database authentication', 1; GO RECONFIGURE; GO ------------------------Create the First Database with Name EmpProfile ------------------ CREATE DATABASE [EmpProfile] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N'Empprofile', FILENAME = N'd:\SQL\DATA\Empprofile.mdf' , SIZE = 102400KB , FILEGROWTH = 51200KB ) LOG ON ( NAME = N'Empprofile_log', FILENAME = N'W:\SQL\DATA\Empprofile_log.ldf' , SIZE = 102400KB , FILEGROWTH = 51200KB ) GO ------------------------Create another Database with Name Empcompensation ------------------ CREATE DATABASE [Empcompensation] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N'Empcompensation', FILENAME = N'd:\SQL\DATA\Empcompensation.mdf' , SIZE = 102400KB , FILEGROWTH = 51200KB ) LOG ON ( NAME = N'Empcompensation_log', FILENAME = N'W:\SQL\DATA\Empcompensation_log.ldf' , SIZE = 102400KB , FILEGROWTH = 51200KB ) GO
Step 2
Let's create the Contained SQL Server Users in the Contained Databases.
-------------------- Create User ProfileUser and CSAppsteam in Empprofile db --------------------------------- USE [Empprofile] GO CREATE USER [ProfileUser] WITH PASSWORD=N'Test$12345', DEFAULT_SCHEMA=[dbo] GO ------ Assign role to user -------- ALTER ROLE [db_datareader] ADD MEMBER [ProfileUser] GO ALTER ROLE [db_datawriter] ADD MEMBER [ProfileUser] Go Grant Execute to ProfileUser GO ------ Creating CSAppsteam User ----------------------------------------------------------------------- CREATE USER [CSAppsteam] WITH PASSWORD=N'My$Test123', DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_datareader] ADD MEMBER [CSAppsteam] GO ALTER ROLE [db_datawriter] ADD MEMBER [CSAppsteam] GO -------------------- Create User HrinfoUser and CSAppsteam in Empcompensation ----------------------- USE [Empcompensation] GO CREATE USER [HrinfoUser] WITH PASSWORD=N'Hr$Test234', DEFAULT_SCHEMA=[dbo] GO CREATE USER [CSAppsteam] WITH PASSWORD=N'My$Test123', DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_datareader] ADD MEMBER [HrinfoUser] GO ALTER ROLE [db_datawriter] ADD MEMBER [HrinfoUser] GO Grant EXECUTE to HrinfoUser GO ALTER ROLE [db_datareader] ADD MEMBER [CSAppsteam] GO ALTER ROLE [db_datawriter] ADD MEMBER [CSAppsteam]
Step 3
Let's query to see if users are created as Contained users or server level users:
Select Authentication_type_desc,* From MASTER.sys.database_principals
WHERE type IN ('U', 'S', 'G') AND authentication_type_desc <>'NONE'
GO
Select Authentication_type_desc,* From EMPCompensation.sys.database_principals
WHERE type IN ('U', 'S', 'G') AND authentication_type_desc <>'NONE'
GO
Select Authentication_type_desc,* From EmpProfile.sys.database_principals
WHERE type IN ('U', 'S', 'G') AND authentication_type_desc <>'NONE'
Step 4
Let's create the table and insert data.
---Create Table in Empprofile db and insert data
Use EmpProfile
GO
Create table Myemp
(
Empno int,
ename varchar(20)
)
-----Inserting few records
Insert into Myemp
Values ('1','a'),
('2','b'),
('3','test4')
---Create Table in Empcompensation db and insert data
Use Empcompensation
GO
Create table Mysalary
(
Empno int,
sal real
)
-----Inserting few records
Insert into Mysalary
values ('1','1200.00'),
('2','1300.00'),
('3','1400.00')
Step 5
Now let's open a new query window in SQL Server Management Studio (SSMS) and login using the ProfileUser user in the EmpProfile database then query the myEMP table to check if the user has access to the data.
Now let's reconnect in SQL Server Management Studio using the CSappsTeam login in the EmpProfile database and query the myEMP table to check if the user has access to the data.
Now let's try to query across databases to the EmpCompensation database using the CSappsTeam user in SSMS. In this circumstance, we get an error as the CSappsTeam user can't see outside the EmpProfile database
Step 6
Now repeat the same steps in SSMS for the HRInfoUser user, to query the MySalary table in the EmpCompensation database.
Now let's reconnect to SSMS using the CSappsTeam user in the EmpCompensation database and query the MySalary table to check if the user has access.
Now let's try to access the MyEmp table across databases from the EmpProfile database using the CSappsTeam user. We will get the same error as above when we try to access data across databases.
select * from EMPPROFILE..Myemp ---you will get following errror---- Msg 916, Level 14, State 1, Line 1 The server principal "S-1-9-3-3240846882-1197223314-1941527945-1512159045." is not able to access the database "EmpProfile" under the current security context.
Step 7
Now that we understand the problem let's take some steps to resolve it. As a first step, let's create the CSappsTeam user in the EmpProfile database with the same SID as the CSappsTeam user in the EmpCompensation database to see if that works.
--Finding SID of CsappsTeam SID in EMPCompensation db USE EMpCompensation GO SELECT name,sid from sys.database_principals where name ='CsAppsTeam' GO
--DROP and RECREATE CsAppsteam user in EmpProfile Database USE [EmpProfile] GO DROP USER CsAppsTeam GO CREATE USER [CsAppsTeam] WITH PASSWORD=N'My$Test123', DEFAULT_SCHEMA=[dbo], sid =0x01050000000000090300000022662BC1922D5C47895DB97345B7215A GO ALTER ROLE [db_datareader] ADD MEMBER [CsAppsTeam] GO ALTER ROLE [db_datawriter] ADD MEMBER [CSAppsteam] GO
Now let's try to access table MyEmp from across databases from the EmpProfile database using the CSappsTeam user and see if we receive an error.
SELECT * from EMPPROFILE..Myemp ---you will get following errror---- Msg 916, Level 14, State 1, Line 1 The server principal "S-1-9-3-3240846882-1197223314-1941527945-1512159045." is not able to access the database "EmpProfile" under the current security context.
Still...No Luck...
Step 8
Now that we have the CSappsTeam user in both databases with the same SID, let's set the EmpCompensation database as trustworthy so that users in the EmpCompensation databases can be trusted by other databases where the username and SID are the same.
---Now Login with Sysadmin role user and set the trustworthy option for the EmpCompensation database ---i.e. one sided only from the EmpCompensation to other databases, but not vice versa Alter Database EmpCompensation Set TRUSTWORTHY ON GO
Now the CSappsTeam user from the EmpCompensation database will be able to access data from other databases, but not vice versa because other databases are not set to trustworthy.
Let's login to SSMS using the CSappsTeam user to the EmpCompensation database then try to access data from the EmpProfile database.
Select * FROM EmpProfile..Myemp GO
Step 9
Now in this final step, let's see if the CSappsTeam user can also update data in the EmpProfile database.
---Login with the CsAppsTeam in the EmpCompensation database
Select e.empno, e.ename, s.sal
From EmpProfile..MyEmp e
Join Mysalary S
ON S.empno = e.empno
GO
-----Now Updating
Update Empprofile..MyEmp Set Ename ='C' where empno=3
Go
---Select to see if it brings updated records
Select e.empno,e.ename,s.sal
From EmpProfile..MyEmp e
Join Mysalary S
ON S.empno = e.empno
Conclusion
In order to achieve your goal, you need to do the following:
- Create a user with same name and same SID for each database
- Turn on the Trustworthy option for the database from where you want to access the data of the other databases.
You can set the trustworthy option on all databases if you want to remove the dependency of accessing data from only one database to another. Note: When you access the data from other databases while running the code from the Trustworthy database; the trusted user uses the privileges of the user (same named user) in the other database rather than privileges of users in the trusted database. Do not directly implement the solution in your production SQL Server environment without weighing the pros and cons as this may expose unneeded security risks. These security risks will be covered in one of my future tips. I encourage you to consider revisiting your security plan to determine the appropriate solution.
Next Steps
- Learn more about Contained Databases on MSSQLTips.com.
- Check out these additional resources:
- http://sqlmag.com/sql-server-2012/sql-server-2012-contained-databases
- http://technet.microsoft.com/en-us/library/ff929055.aspx
- http://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx
- http://www.mssqltips.com/sqlservertip/2792/sql-server-contained-databases-for-database-refreshes/
- http://www.sqlsoldier.com/wp/sqlserver/crossdatabasequeriesbycontainedusers
About the author
Varinder Singh is a SQL Server production DBA in the Boston area with 20+ years of database, SSIS, SSAS and SSRS administration experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-05-30
