RE: Security context of stored procedure

From: Baisong Wei[MSFT] (v-baiwei_at_online.microsoft.com)
Date: 03/02/04

  • Next message: AG: "Re: SSL setup problems" 
    Date: Tue, 02 Mar 2004 07:58:46 GMT

    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.

  • Next message: AG: "Re: SSL setup problems"

    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: 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 Procedures in the Master Database
      ... The error occurs on a GRANT, not EXEC. ... >>I have a stored procedure that I want to make available to ... >>following command in the master database: ...
      (microsoft.public.sqlserver.security)
    • Re: stored procedure xp_cmdshell
      ... It is generally a very bad thing to ever grant xp_cmdshell to a anyone. ... Granted you'll have to give this permission to the user ASP.NET is running ... I was originally planning to have the images to SQL, ... > xp_cmdshell stored procedure and of course the master database (which ...
      (microsoft.public.sqlserver.programming)