Solved

Database property to prevent cross database queries

Posted on 2013-02-07
11
350 Views
Last Modified: 2013-03-12
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,
0
Comment
Question by:UConn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38864386
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
 

Author Comment

by:UConn
ID: 38864432
we are trying to control queries going across databases within one instance. Is there a way to enforce security for logins/ database?
thanks,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38865428
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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:UConn
ID: 38893439
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38895108
Did you read up on doing the following:
ALTER DATABASE YourDatabaseNameGoesHere SET DB_CHAINING OFF

If so, what part was not clear.
0
 

Author Comment

by:UConn
ID: 38914159
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38916807
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
 

Author Comment

by:UConn
ID: 38918198
that would be very helpful
thank you
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38921956
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
 

Author Comment

by:UConn
ID: 38927270
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38979640
You awarded a "B" grade was there something I could have done differently in order to completely satisfy you?
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question