Post reply

Stored procedures / permissions

  • October 21, 2003 at 10:05 am

    #82811

    Everything worked fine last week and the only thing that has happened in between is an upgrade to sql2000.

    Two databases - the user only has permissions to run an sp on one of the them and this calls about 60 other sp's. The user is now getting permissions denied on some tables (but not all). So not only did this work last week but I can't see any reason why only part doesn't work this week. I thought that the permissions were on the stored procedures and that was how things should be done .... HELP - as I'm not sure even to go about this apart from to grant explicit permissions to the tables for each user.

  • October 21, 2003 at 10:08 am

    #478977

    Did ownership change on any of the objects in question, thereby breaking the ownership chains?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • October 21, 2003 at 10:11 am

    #478978

    Thanks for replying - no, nothing has changed (me thinks) the dbs were dettached/attached. All objects are dbo.

  • October 21, 2003 at 1:18 pm

    #478996

    Two questions:

    1) Did you upgrade to sql2000 sp3?

    2) Is the stored procedure in the first database executing a stored procedure in the second database?

    There is a new configuration setting called cross-database ownership chaining which can be set at either the server level or the database level. By default, this new setting is disabled. Simply put, this new setting controls the permissions within an sp when crossing databases. In a nutshell, suppose you had sp1 in db1 and sp2 in db2 and sp1 calls sp2. If the permission is disabled, when userA executes sp1, any statements within sp2 are executed in the security context of userA. When the setting is enabled, any statements within sp2 are executed in the security context of the procedure owner.

    So assume sp2 does a select against TableA. With cross-db ownership chaining disabled, userA will get an error message when executing sp1 unless they have been granted select permission on TableA in db2. With the ownership enabled, sp1 will run fine because the select will execute as if dbo was executing the select.

    /John

  • October 22, 2003 at 5:24 am

    #479064

    Thank you John! This has solved my problem.

    Elaine

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

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