Cross database access when executing stored procedures

Executing a stored procedure that draws from tables on other databases requires the option of cross-database ownership chaining. SQL Server development expert shares what you'll need to investigate this server option.

Greg Low

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

In VB.NET, I am trying to execute a stored procedure that tries to insert values into a table using a SELECT statement. The SELECT statement uses tables from this database and also uses a view containing this database's tables and a table from a different database. When I try to execute the procedure, I get an error message that the user ID in my connection string does not have rights to the different database. What would be the solution to this problem?

The thing to investigate here that will allow access across databases is cross-database ownership chaining. The behavior of this function was modified in SQL Server 2000's Service Pack 3. Take a look at the entry in Books Online called "Cross DB Ownership Chaining option." Make sure you have an updated copy of Books Online too.

This was last published in August 2006

Dig Deeper on SQL Server Stored Procedures

All
News
Get Started
Manage
Problem Solve

Related Q&A from Greg Low

Query to find SQL Server database names created by users

Find how to query in SQL Server to get all database names created by users. Continue Reading

Problem attaching stored procedure and SQL Server trigger

Learn why using the sp_OACreate stored procedure with a SQL Server 2005 trigger, is likely to result in a permissions problem. Continue Reading

Schedule stored procedure and save output to network drive

Find how to save stored procedure output to the network drive via SQL Server Management Studio. Continue Reading

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our SQL Server experts

View all SQL Server questions and answers

SearchBusinessAnalytics

Tableau acquisition of MIT AI startup aims at smarter BI software

Tableau acquires AI startup Empirical Systems in a plan to provide users with automated data modeling capabilities and enable ...

Choosing the right data discovery platform for the enterprise

Obviously, cost and scalability factor into data discovery platform buying decisions, but other features, like visualizations and...

BI vendors aim to ease visual data analysis by business users

Despite the self-service BI push, enabling end users to take the lead on analyzing and visualizing data is often still a ...

SearchDataCenter

Evaluate converged vs. hyper-converged infrastructure benefits

Many IT admins wonder if their data center can benefit from hyper-converged or converged infrastructure. To choose the right one,...

Do I need converged or hyper-converged infrastructure appliances?

Scalability, risk tolerance and cost all factor into the decision between converged and hyper-converged infrastructure. The two ...

Data center monitoring best practices simplify software choice

Metrics are essential to keep any data center operating smoothly. With monitoring software and best practices, admins can ...

SearchDataManagement

Hortonworks cloud options grow via Google, Microsoft, IBM

Hortonworks now supports Google Cloud Storage and has also broadened cloud deals with Microsoft and IBM, aiming to increase cloud...

Evolving data integration strategies target new analytics needs

Most companies don't have a shortage of data, but it's often stored in siloed systems or inconsistent formats -- problems that ...

What goes into a customer analytics data integration framework

Customer data integration is a minefield for IT teams to navigate. But incorporating a set of core technical functions into an ...

SearchAWS

AWS termination protection acts as resource safety belt

In the push to get applications to production quickly, mistakes can happen. But you can avoid potentially disastrous AWS resource...

How AWS Fargate reduces container management burdens

Enterprises face a number of management challenges when they adopt containers. While AWS Fargate eliminates some of these hurdles...

Pave a lasting AWS career path

While cloud certifications might help you land an AWS job, most employers look for IT pros with a mix of training and hands-on ...

SearchOracle

AI-for-IT-operations tools map Vegas systems to Oracle ERP

Mapping IoT activity and other IT data to Oracle ERP is the task Las Vegas set for FixStream's AIOps platform in an effort to ...

Oracle 18c features give DBAs incremental database capabilities

Oracle Database 18c is really another version of 12c Release 2 with a different name. It doesn't contain major new features, but ...

Cloud workloads see gradual uptake, but miles to go for databases

A picture of steady but not rapid migration to the cloud by Oracle users emerged at Collaborate 18, with database vets saying ...

SearchContentManagement

Cloud DAM platforms vs. on premises for digital asset management

Learn about digital asset management and how cloud DAM democratizes media management and delivery across the enterprise while ...

Box workflow gets upgrade with Progressly purchase

Box is hoping to upgrade its workflow capabilities by adding the startup Progressly to its team, while also still partnering with...

ECM gets a show of support from Zendesk Guide Enterprise

Guide Enterprise captures insights and curates knowledge bases for customer service representatives and customers while helping ...

SearchWindowsServer

Thorough Exchange Server testing regimen eliminates doubt

Don't assume your Exchange 2016 implementation will work because you bought the right gear. Follow these pointers to execute a ...

Organize Active Directory with these strategies

Administrators tasked with cleaning up the Active Directory user group structure need not search any further. Use these tips to ...

Adobe zero-day fix precedes June Patch Tuesday

Adobe closed a zero-day vulnerability last week, which Microsoft added to its June Patch Tuesday updates. Administrators have a ...

SQL Server stored procedures vs. CLR stored procedures: Time to migrate?

Check SQL Server database and log file size with this stored procedure

Configure SQL Server Service Broker for sending stored procedure data

Find size of SQL Server tables and other objects with stored procedure

Saying yes, no or maybe to stored procedures

Why use stored procedures?

library

trigger

Change collation to SQL Server 2005 after upgrading databases

Stored procedures: Create and manage standard objects

Stored procedure: Investigate related data

Stored procedures: Create and delete constraints and indexes