Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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,
0
UConn
Asked:
UConn
  • 5
  • 5
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Anthony PerkinsCommented:
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
 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now