Re: Controlling Access to data from multiple databases
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 16 Sep 2008 15:04:35 -0700
Cqlboy (Cqlboy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
How do I control data access to several databases for a targeted Windows
Group? I created a stored procedure and granted "Execute" permissions but
this doesn't work since within the body of the stored procedure it reaches
out to read tables in other databases which, in turn, is why my stored
procedure fails on permissions. I can only solve the problem by granting
read only permission to all databases my stored procedure depends on. I
thought I only had to give permission to my stored procedure, not every
object it depends on? Thanks. -CqlBoy
For this to work, you need to 1) enable db-chaining 2) make sure that
the databases have the same owner. By default cross db-chaining is turned
off, as it is not a secure on a server where different people own
different databases, and typically do have access to each other's databases.
On the other hand, on a server where the DBA is control over all databases,
enabling DB-chaning is not a risk.
I have some details on http://www.sommarskog.se/grantperm.html#dbchainging
(which is part of a longer article).
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Controlling Access to data from multiple databases
- From: Cqlboy
- Controlling Access to data from multiple databases
- Prev by Date: Unable to connect to new WSUS 3.0 remote SQL DB using mmc console
- Next by Date: Error 15404 - SQL 2005
- Previous by thread: Re: Controlling Access to data from multiple databases
- Next by thread: Integrated Security
- Index(es):
Relevant Pages
|
