Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enabling Cross DB Access to Contained SQL Server Users in Partial Contained Databases


By:   |   Last Updated: 2014-05-30   |   Comments (9)   |   Related Tips: More > 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'



Enabling Cross DB Access to Contained SQL Server Users in Partial Contained Databases

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.

ogin using Profileuser in Empprofile database

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 reconnect using CsAppsTeam in Empprofile db

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

query the cross database EmpCompensation using CsAppsTeam

Step 6

Now repeat the same steps in SSMS for the HRInfoUser user, to query the MySalary table in the EmpCompensation database.

repeat the same steps for HRInfoUser , CsAppsTeamin EmpCompensation db

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.

reconnect using CsAppsTeam in EmpCompensation db

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.



select * from EMPPROFILE..Myemp

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


Finding SID of CsappsTeam SID in EMPCompensation db


--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.



let's try to access the table MyEmp from Empprofile database using CsAppsTeam user

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 as have CsAppsTeam user in both databases with Same SID

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


access the data from other databases

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


let's see if CsAppsTeam can also update data in Empprofile

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


Last Updated: 2014-05-30


next webcast button


next tip button



About the author
MSSQLTips author Varinder Sohal Varinder Singh is a SQL Server production DBA in the Boston area with 20+ years of database, SSIS, SSAS and SSRS administration experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 09, 2014 - 5:38:57 PM - JD Singh Back To Top

Great article, Thanks for sharing, I like it..


Thursday, June 05, 2014 - 10:44:08 AM - Frank Lizardo Back To Top

I like your article Varinder. Congrats. We can use same approach to grant access to sp_send_dbmail and SQL Agent roles for contained database users.

Great article. I love it.

 


Thursday, June 05, 2014 - 10:38:11 AM - Suresh Back To Top

Nice article and thanks for sharing your ideas and thoughts.


Tuesday, June 03, 2014 - 12:58:05 PM - Naga Back To Top

Great Article!!!!


Tuesday, June 03, 2014 - 12:16:08 PM - Dhwani Shah Back To Top

Very good Article. Explained thoroughly with good details. Worth reading. Thanks Varinder


Tuesday, June 03, 2014 - 12:45:55 AM - Aditya Krishna Back To Top

 

This article is very understanding and helpful, its a very good one.


Monday, June 02, 2014 - 10:03:19 AM - Shivakumar Kondu Back To Top

Worth reading of this article about Trustworthy with particulars...Simply awesome!!!


Monday, June 02, 2014 - 12:56:16 AM - Imanpreet Singh Back To Top

Great Article, worth reading. Trustworthy concept is known to most of the DBAs but how to apply the same is a bit challenge. Thanks Varinder Singh.


Friday, May 30, 2014 - 9:14:15 AM - Jeremy Kadlec Back To Top

Varinder,

Congrats on your first tip and welcome to the team!

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Learn more about SQL Server tools