Post reply

Questions about permission via Stored Procedures

  • June 29, 2010 at 11:07 am

    #100269

    In my MSSQL Server 2000:

    - There is an user named "USER1"

    - There is a database "DB1" and USER1 has public right only on DB1

    - In "DB1" there are 2 tables: [dbo].[Table1] and [user1].[Table2]

    - There is stored procedures "STORED1":

    CREATE PROCEDURE [STORED1] AS

    select * from [dbo].[Table1]

    GO

    - There is stored procedures "STORED2":

    CREATE PROCEDURE [STORED2] AS

    EXEC ('select * from [dbo].[Table1]')

    GO

    - There is stored procedures "STORED3":

    CREATE PROCEDURE [STORED3] AS

    EXEC ('select * from [user1].[Table2]')

    GO

    - USER1 is grant to execute STORED1, STORED2 and STORED3

    When I use SQL Analyzer, DB1 via USER1

    - I execute STORED1 --> it's OK

    - But I execute STORED2 --> there is error "SELECT permission denied on object 'Table1', database 'DB1', owner 'dbo'"

    Why is it???

    I want that USER1 can only access [user1].[Table2] via STORED3 and prevent him to access directly [user1].[Table2] (he can't execute SELECT directly). How can I do???

    Thanks a lots

  • June 29, 2010 at 11:16 am

    #1186551

    the issue is the dynamic SQL; if that is used in a proc, the calling user needs to have the rights to the underlying object for whatever the proc is doing.

    if your procedure is using dynamic SQL, you'd need to use EXECUTE AS in order to allow your end user to use the STORED2 or STORED3, but still prevent him from direct access to the underlying table.

    I'm not sure the EXECUTE AS clause was available in SQL 2000...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 30, 2010 at 12:48 am

    #1186870

    Thanks for your advice!

    Could you help me next question:

    USER1 is owner of [user1].[Table2]

    How to PREVENT him access (SELECT, INSERT, UPDATE, DELETE) to [user1].[Table2] directly, I want to let him just only can access via Stored Procedures STORED3

    That means in SQL Analyzer:

    He can: Exec STORED3

    But he can't: SELECT/INSERT/UPDATE/DELETE from [user1].[Table2] ......

    Thanks again!

  • July 7, 2010 at 9:00 am

    #1189670

    Any body comments please!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply