Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database property to prevent cross database queries

Posted on 2013-02-07
11
Medium Priority
?
364 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 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