Re: Security context of stored procedure

From: A.M (IHateSpam_at_sapm123.com)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 09:23:44 -0500

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: T-SQL Debugger Error
    ... denied on object 'sp_sdidebug', database 'master', owner 'dbo'. ... and when I grant the permission of sp_sdidebug, ... I am using following statement to grant the permission. ...
    (comp.databases.ms-sqlserver)
  • 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: Extended Stored Procedure: Get the current db of the client
    ... with the caveat that you don't recommend it because Microsoft ... of a stored procedure versus umpteen of the same stored procedure spread ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ...
    (microsoft.public.sqlserver.odbc)