Database property to prevent cross database queries

Hello,
I was wondering if there was a way in SQL Server to prevent cross database querying? would this need to be scripted or is there a option for this?

thanks,
UConnAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Here you have a complete example:

USE master
GO 

-- Create login to test with
CREATE LOGIN TestLogin WITH PASSWORD = '<yourverystrongpasswordgoeshere!>'
GO 

-- Create two databases
CREATE DATABASE DatabaseA
GO 
CREATE DATABASE DatabaseB
GO

-- Set ownership 
ALTER AUTHORIZATION ON DATABASE::DatabaseA TO sa
ALTER AUTHORIZATION ON DATABASE::DatabaseB TO sa
GO

-- Make sure Cross-Database Chaining is not enabled
ALTER DATABASE DatabaseA SET DB_CHAINING OFF
ALTER DATABASE DatabaseB SET DB_CHAINING OFF
GO

USE DatabaseB
GO

-- Give access to DatabaseB
CREATE USER TestUser FOR LOGIN TestLogin
GO 

-- Create a Test Table in DatabaseB
CREATE TABLE dbo.TestTable (ID integer)
GO

USE DatabaseA
GO

-- Give access to DatabaseA
CREATE USER TestUser FOR LOGIN TestLogin
GO 

-- Create a Stored Procedure in DatabaseA
CREATE PROCEDURE dbo.usp_MyProc
AS

SELECT ID
FROM DatabaseB.dbo.TestTable

GO 

-- With the appropriate Permissions
GRANT EXECUTE ON dbo.usp_MyProc TO TestUser
GO 

-- Now try and execute that Stored Procedure with Cross-Database Ownership Chaining is off
USE DatabaseA
GO 
EXECUTE AS LOGIN = 'TestLogin'
GO 
EXEC dbo.usp_MyProc
GO 
REVERT;
GO


-- Now turn on Cross-Database Ownership Chaining and try again
ALTER DATABASE DatabaseA SET DB_CHAINING ON
ALTER DATABASE DatabaseB SET DB_CHAINING ON
GO

USE DatabaseA
GO 
EXECUTE AS LOGIN = 'TestLogin'
GO 
EXEC dbo.usp_MyProc
GO 
REVERT;
GO


---- Clean-up
USE MASTER;
GO 
DROP DATABASE DatabaseA;
GO 
DROP DATABASE DatabaseB;
GO
DROP LOGIN TestLogin
GO 

Open in new window


The output should look like this:
Msg 229, Level 14, State 5, Procedure usp_MyProc, Line 6
The SELECT permission was denied on the object 'TestTable', database 'DatabaseB', schema 'dbo'.

(0 row(s) affected)
0
 
LowfatspreadCommented:
don't think there is any option to specifically control this ...

can you give us some background as to what you are trying to avoid...

once you give access to the databases/objects they can be used in any combination,,
0
 
UConnAuthor Commented:
we are trying to control queries going across databases within one instance. Is there a way to enforce security for logins/ database?
thanks,
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
we are trying to control queries going across databases within one instance.
By default this is disabled.  If it is enabled you can disable it using the following:
ALTER DATABASE YourDatabaseNameGoesHere SET DB_CHAINING OFF
0
 
UConnAuthor Commented:
I am looking for further insight on this question. Please advise.
I am looking for cross database querying based scenarios rather than database ownership.
thanks
0
 
Anthony PerkinsCommented:
Did you read up on doing the following:
ALTER DATABASE YourDatabaseNameGoesHere SET DB_CHAINING OFF

If so, what part was not clear.
0
 
UConnAuthor Commented:
we are not looking for database ownership and turning on the db_chaining indicator is related to database ownership properties of a database. We are specifically looking for any features/ security settings available within SSMS which would PREVENT cross database querying.
thanks
0
 
Anthony PerkinsCommented:
I am going to have to beg to differ with you: Cross-Database Ownership Chaining was specifically added in SQL Server 2000 SP2 to address your question.  But if you feel it does not address your question, perhaps you can tell me why that is not the case.  It is trivial to test and you can confirm in all of one minute.  Let me know if you need a script to do that and I will be happy to provide.
0
 
UConnAuthor Commented:
that would be very helpful
thank you
0
 
UConnAuthor Commented:
ok.. thank you.. I will check with my team and get back to you.
I did try the script and it restricted permissions to execute stored when database ownership is turned off.
0
 
Anthony PerkinsCommented:
You awarded a "B" grade was there something I could have done differently in order to completely satisfy you?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.