Solved

Database property to prevent cross database queries

Posted on 2013-02-07
11
343 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
connection to SQL 2012 error in windows 10 18 46
how to resize column length with primary ket 4 33
What does "Between" mean? 6 47
relocating SQL 2000 18 32
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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