Re: Security context of stored procedure

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 08:41:12 -0600

> The only thing that still is not clear is: do we have same roll in cross
> database queries?

The same considerations Biasing discussed apply to cross-database queries;
as long as the same login owns all of the objects involved, the ownership
chain is unbroken and permissions on indirectly referenced objects are not
needed. However, beginning with SQL 2000 SP3, the cross-database chaining
option also needs to be enabled.

Note that for dbo-owned objects in cross-database queries, the owners of
these databases need to be the same so that the dbo user maps to the same
login and thereby owns the objects in both databases. If needed. you can
execute sp_changedbowner for these databases so that they have a common
owner. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"A.M" <IHateSpam@sapm123.com> wrote in message
news:%23QrlvIGAEHA.3284@TK2MSFTNGP09.phx.gbl...
> Thank you Baisong. That was a comprehensive answer.
>
> The only thing that still is not clear is: do we have same rull in cross
> database queries?
>
> It seems that cross database tables are completely blocked from stored
> procedure caller unless the user have explicit select to the cross
database
> table. Am I correct?
>
>
>
> Thanks again,
>
> Ali
>
>
>
>
>
> "Baisong Wei[MSFT]" <v-baiwei@online.microsoft.com> wrote in message
> news:TbmuzwCAEHA.836@cpmsftngxa06.phx.gbl...
> > Hi Ali,
> >
> > Thank you for using the newsgroup and it is my pleasure to help you with
> > you issue.
> >
> > Before I give an explanation, I should give an sample first.
> >
> > Suppose there are three users in a database TEST_CHAIN, which has a
table
> > 'AUTHORS':
> >
> > test_sp1: the db_owner
> > test_sp2: a common user, who have no SELECT permission on table
'AUTHORS'
> > test_sp3: not a dbo, but he is in the role of db_dlladmin and could
select
> > data from 'AUTHORS'
> >
> > you could login into the database from QA by 'test_sp1' and create a
> simple
> > stored procedure:
> > use test_chain
> > go
> > create proc au_all
> > as
> > select * from authors
> >
> > --then grant au_all to test_sp2
> >
> > grant exec
> > on
> > au_all
> > to
> > test_sp2
> > go
> >
> > --You could run 'exec sp_help au_all', its owner is 'test_sp1'
> >
> > Then you could login into the database from QA by 'test_sp3' and create
a
> > simple stored procedure:
> > use test_chain
> > go
> > create proc au_all2
> > as
> > select * from authors
> >
> > --then, also grant it to test_sp2
> > grant exec
> > on
> > au_all
> > to
> > test_sp2
> > go
> >
> > --You could run 'exec sp_help au_all2', its owner is 'test_sp3'
> >
> > When login into the database from QA by the 'test_sp2', when you run
> > 'select * from authors', you will get error message:
> > SELECT permission denied on object 'authors', database 'test_chain',
owner
> > 'dbo'.
> >
> > When you run 'exec dbo.au_all', you will get all the record in the table
> > 'AUTHORS'
> > When you run 'exec test_sp3.au_all2', you will get the error message:
> > SELECT permission denied on object 'authors', database 'test_chain',
owner
> > 'dbo'.
> >
> > As my understanding of the question, if the storedProc1 is running by
> > UserA. There is some scenario:
> >
> > Typically, the owner of a stored procedure also owns the underlying
> objects
> > (other views or tables), when grant permissions to the stored procedure
to
> > a user (test_sp2), it will have all the permission defined in the stored
> > procedure (as the dbo). SQL Server will not check the permission of the
> > test_sp2.You should be sure when grant permissions to others. If the
> > permission grant process will envolve the object ( such as the owner of
> the
> > table 'AUTHORS' is not test_sp3, but user 'test_sp3' grant the stored
> > procedure 'au_all2' which will select the 'Authors' to user 'test_sp2),
it
> > will check the permission of the user on this object.
> >
> > Hope this helps and if you still have questions, please feel free to
post
> > your message here and I am glad to help.
> > Thanks
> >
> > Baisong Wei
> > Microsoft Online Support
> > ----------------------------------------------------
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only. Thanks.
> >
> >
> >
>
>

Relevant Pages

  • Re: Execute stored procedure only
    ... right to execute stored procedure in database called "mydatabase". ... deny on every permission and checked grant on execute. ...
    (microsoft.public.sqlserver.security)
  • RE: Security context of stored procedure
    ... Suppose there are three users in a database TEST_CHAIN, ... simple stored procedure: ... also grant it to test_sp2 ... SELECT permission denied on object 'authors', database 'test_chain', owner ...
    (microsoft.public.sqlserver.security)
  • Re: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure to drop and create table for end user
    ... > having permission on the database that they should not (like delete rows and ... > such) so the only thing the end user has access to is execute permission on ... > back with select permission not granted on a table the stored procedure is ... > tables (which i dont like doing considering some of them have sensative data ...
    (microsoft.public.sqlserver.programming)
  • Re: code access security
    ... Error 1 CREATE ASSEMBLY for assembly 'GmsSqlClr' failed because assembly ... owner has EXTERNAL ACCESS ASSEMBLY permission and the database has the ... make sure the database owner is mapped to the correct login on ...
    (microsoft.public.dotnet.languages.csharp)