Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / February 2008

Tip: Looking for answers? Try searching our database.

URGENT: Denied access when connecting to a different database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Gaspar - 22 Feb 2008 15:30 GMT
DATABASE1 is the default database. I'm using SQL2005 with SQL
authentication.
In a stored procedure I must query a table from a different database using
SELECT DATABASE2.dbo.MYTABLE. (DATABASE2 is readonly).

When I run the procedure I get a security exception telling me that the
current user as no access to Database2 (because the user doesn't exists in
database2)

What should I do?
- Enable guest account for database2?
- Map user from database1 to another user in database2?
I tried with impersonation but I has no luck.

I need your help as soon as possible.
Thanks a lot,
Gaspar
Gaspar - 22 Feb 2008 16:00 GMT
I found an answer in online books, but it's not as simple as I supposed.
Here's an extract.
Thanks anyway.

****************
When impersonating a principal by using the EXECUTE AS LOGIN statement, or
within a server-scoped module by using the EXECUTE AS clause, the scope of
the impersonation is server-wide. This means that after the context switch,
any resource within the server that the impersonated login has permissions
on can be accessed.

However, when impersonating a principal by using the EXECUTE AS USER
statement, or within a database-scoped module by using the EXECUTE AS
clause, the scope of impersonation is restricted to the database by default.
This means that references to objects outside the scope of the database will
return an error. To understand the reason for this default behavior,
consider the following scenario.

It is possible that the owner of a database, while having full permissions
within that database, does not have any permissions outside the scope of the
database. Therefore, SQL Server does not allow the database owner to
impersonate, or grant someone else the ability to impersonate, another user
in order to access resources beyond the scope of the current permissions of
the database owner.

For example, consider two databases in a hosting environment and each
database belongs to a separate owning entity. Database 1 is owned by Bob and
Database 2 is owned by Fred. Neither Bob nor Fred wants the other to access
resources within their respective databases. As the owner of Database 1, Bob
can create a user for Fred in his database and because he has full
permissions within Database 1, Bob can also impersonate user Fred. However,
because of the security restrictions imposed by SQL Server, Bob cannot
access Fred's database under the impersonated context. Without these default
restrictions in place, Bob would be able to access Fred's data without his
knowledge. This is why the scope of database-level impersonations is bounded
by the database by default.

However, in certain scenarios it may be useful to selectively extend the
scope of impersonation beyond the database. For example, this would be the
case with an application that uses two databases and requires access to one
database from the other database.

Consider the case of a marketing application that invokes a stored procedure
named GetSalesProjections in the Marketing database and the stored procedure
has an execution context switch defined in it. The stored procedure calls
into the Sales database to retrieve sales information from the SalesStats
table. By default, this scenario will not work, because an execution context
established inside one database is not valid outside that database. However,
the developers of the marketing application do not want users of the
marketing application to have direct access to the Sales database or have
permissions on any objects within it. The ideal solution would be to use the
EXECUTE AS clause in the stored procedure to impersonate a user that has the
required permissions in the Sales database. However, the default
restrictions currently in place prevent this. So, the question is how can
the developers resolve this problem.

In SQL Server 2005, you can selectively extend the scope of the database
impersonation established within a database by establishing a trust model
between the two databases. However, before describing this trust model and
how the scope of the impersonation can be selectively extended, you should
understand authentication and the role of authenticators in SQL Server.

****************
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.