Application Roles for Cross-Database Joins

Jason_Schaitel

I have an application that segregates data into two different
databases. Database A has stored procs that perform joins between
tables in database A and database B. I am thinking that I have reached
the limits of Application Roles, but correct me if I am wrong.
My application creates a connection to database A as 'testuser' with
read only access, then executes sp_setapprole to gain read write
permissions. Even then the only way 'testuser' can get data out of the
databases is via stored procs or views, no access to tables directly.
Anyone know of a solution? Here is the error I get:

Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationI nfo, Line
38
Server user 'testuser' is not a valid user in database 'DatabaseB'

The system user is in fact in database A and B.

thanks

Jason Schaitel

Aug 25 '05 #1

Subscribe Reply

5758

Erland Sommarskog

Jason_Schaitel (ja************ @hotmail.com) writes:

I have an application that segregates data into two different
databases. Database A has stored procs that perform joins between
tables in database A and database B. I am thinking that I have reached
the limits of Application Roles, but correct me if I am wrong.
My application creates a connection to database A as 'testuser' with
read only access, then executes sp_setapprole to gain read write
permissions. Even then the only way 'testuser' can get data out of the
databases is via stored procs or views, no access to tables directly.
Anyone know of a solution? Here is the error I get:

Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationI nfo, Line
38
Server user 'testuser' is not a valid user in database 'DatabaseB'

The system user is in fact in database A and B.

Books Online says:

When an application role is activated, the permissions usually
associated with the user's connection that activated the application
role are ignored. The user's connection gains the permissions
associated with the application role for the database in which the
application role is defined. The user's connection can gain access to
another database only through permissions granted to the guest user
account in that database. Therefore, if the guest user account does not
exist in a database, the connection cannot gain access to that
database.

That is, once you have set the application role in A, you are someone
else, and your access outside A is limited.

The one way I can think of to sort this out - beside uniting the databases
into one - is to enable the server configuration parameter "Cross DB
Ownership Chaining". This option was added in SP3 is off by default.
If there no other databases from other applications on the server,
there is no problem to enable this option. However, on consolidated
server that hosts databases for unrelated applications, this is not
recommendable.

For cross DB chaining to work, the databases must also have the same
owner.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 25 '05 #2

Dan Guzman

The one way I can think of to sort this out - beside uniting the databases
into one - is to enable the server configuration parameter "Cross DB
Ownership Chaining". This option was added in SP3 is off by default.
If there no other databases from other applications on the server,
there is no problem to enable this option. However, on consolidated
server that hosts databases for unrelated applications, this is not
recommendable.

Jason could instead enable the 'db chaining' database option for only those
databases needed by the application rather than turning the cross-database
chaining server-wide.
For cross DB chaining to work, the databases must also have the same
owner.
This is true, assuming the objects are owned by 'dbo', because database
ownership determines the dbo user mapping. In the case of non-dbo-owned
objects, the object owners in the different databases need to map to the
same login in order to maintain an unbroken ownership chain.
The user's connection can gain access to
another database only through permissions granted to the guest user
account in that database. Therefore, if the guest user account does not
exist in a database, the connection cannot gain access to that
database.

To expand on this BOL excerpt, it's necessary to enable the guest user in
the non-application role databases so that users have a security context
after the application role is enabled. However, no permissions need to be
granted to guest or public in Jason's situation because access is done only
through views and procs from application role database.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Aug 26 '05 #3

Jason_Schaitel

I have tried to look in BOL and Google Groups for the how to enable the
cross database ownership chaining option at the database level and not
having much luck. Can you point me to it?

thanks

Jason

Aug 30 '05 #4

Erland Sommarskog

Jason_Schaitel (ja************ @hotmail.com) writes:

I have tried to look in BOL and Google Groups for the how to enable the
cross database ownership chaining option at the database level and not
having much luck. Can you point me to it?

exec sp_dboption yourdb, 'db chaining', true

This option is not in the original Books Online, as it was added in SP3.
But it is in the updated Books Online, see link below.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 30 '05 #5

Similar topics

1863

Application roles

by: Prashant Thakwani | last post by:

Can anybody tell, how to implement the application roles in SQL Server 2000. Basically, I want to Implement the application roles in our application, so that it can be application specific. Its' an clients requirement from we people. Thanks Prashant Thakwani

Microsoft SQL Server

5392

Server Error in '/' Application. -> web.config

by: tommy | last post by:

hello everbody, i write a little asp-application with forms-authentication. i copy my aspx-files with web.config to my webspace and i get the error above... i tried to set the custom-errors-tag ="off" but nothing happens.... what is my failure????

ASP.NET

375

Server Error in '/' Application.

by: Sean | last post by:

HI There, I am having trouble deploying my .aspx pages to a remote server, I have made changes to the config file and it still returns an error. I have also contacted the server administrator to create an application in IIS, what other checks can I make? Sean !-- error mesage

ASP.NET

1622

web application security

by: Stephen | last post by:

I have my intranet setup on our web server. It contains multiple applications, but none are set up in the default application pools. In other words, I create a webform and plop it into a directory on the web server. My question revolves around security models for the applications. I have been rethinking my current security strategy, which is basically as follows: dim strUser as string=ucase(User.Identity.Name) dim boolAccess as...

ASP.NET

5717

SQL Server Application Roles

by: Jonathan Allen | last post by:

Is this the correct way to use application roles? Public Function GetDBConnection() As SqlConnection Dim oCon As New SqlConnection(myConnectionString) oCon.Open() Using oCmd As SqlCommand = oCon.CreateCommand oCmd.CommandText = "IF NOT User_NAME() = 'app_name' Exec sp_setapprole 'app_name' ,'password" oCmd.ExecuteNonQuery() End Using

C# / C Sharp

3998

application roles

by: bill | last post by:

I am looking for examples and assistance in configuring application roles using SQL Server 2000 and VB.NET, both web forms and windows forms. Are there any suggestions? Thanks Bill

Visual Basic .NET

3521

Implementing SSO for Smart Client Application which uses Web Servi

by: Rasheed | last post by:

We are building a smart client application (.NET 2.0) which uses Web Services to access the business objects. Services: The Web Services have been secured by brokered authentication using X509 certificate along with message level security. (Right now the web services are consumed only by the smart client application in intranet environment. However, few services would be exposed as enterprise services in future. Such services can be...

.NET Framework

4498

ASP.NET 2.0 Authorization based on Combination of Allow/Deny Users/Roles.

by: Douglas J. Badin | last post by:

Hi, The problem with Authorization is it stops at the first match and doesn't permit Grouping. On the Web Site, I am trying to Secure Page Access and SiteNaviagation by implementing the following ASP.NET 2.0 features: - Membership - Site Maps

ASP.NET

2697

MenuitemDataBound With roles

by: sidhuasp | last post by:

Hi everyone I am using a mainmenu witeh sitemap provider with folowing sitemap <siteMapNode> <siteMapNode url="" title="Master Data" description="Enter Master data" roles ="Admin,PM"> <siteMapNode url="~/PL/Master/ViewProjects.aspx" title="Projects" description="View/Add Projects" roles ="Admin,PM" /> <siteMapNode url="~/PL/Master/AssignProject.aspx" title="Assign Project" description="Enter User Details details" roles...

.NET Framework

3915

Application Roles and SQL Server 2005 - DDL?

by: JimL | last post by:

Hello group, We have recently come up with a problem where SQLServer 2005 differs from 2000. We have an application role, which needs to run DDL to alter tables etc. The documentation for 2005 states quite clearly that application roles cannot be made members of built in roles like db_owner or db_ddladmin - which proves to be the case, but this was possible in 2000.

Microsoft SQL Server

9563

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9386

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10145

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9938

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9822

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5406

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3917

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3523

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2793

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General