Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
I am really confused

Answered I am really confused

  • Tuesday, March 19, 2013 6:29 PM
     
     
    Sign In to Vote
    0

    Sorry - this is elementary but not for me today....:-(

    Using SQL 2012

    I have two databases

    A

    and

    B

    and one account "account" using the default dbo schema.

    cross chaining is turned on.

    In A, "account" has exec permission on a stored procedure that does an insert in B
    I am getting an error that "account" does not insert permission on table in B
    ?????

    In Sql 2000 - I NEVER had to give explicit IUD permissions on a table when using a Sproc....

    the whole point is that I dont want specific permissions on any of the tables...

    ????
    what am i missing

    Thanks

    T

    GADOI

     

All Replies

  • Tuesday, March 19, 2013 6:37 PM
     
     
    Sign In to Vote
    0

    I just saw this posted earlier and it seems to apply... because i am using dynamic SQL (I have to in this instance)

    how is your application connecting to sql server...let's says it is connecting as userA, so, in the management under that database context, run this

    USE <<DATABASENAME>> GO Grant execute  on <<PROCEDURENAME>> to USERA .. this should give execute permissions to the userA for that Procedure. If the procedure has dynamic sql , you should grant access to the tables as well.

    Hope it Helps!!

    GADOI

     
  • Thursday, March 21, 2013 3:05 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    This behavior is called ownership chaining in SQL Server.

    When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.
    In that case, if the stored procedure and the table has same owner, we do not need to grant those permissions at the table level, execute permission on a stored procedure is sufficient.  When the table has difference owner as the stored procedure, full information about permissions on this table is retrieved. We should grant permission on table.

    For more information, please see: Ownership Chains

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.

    Fanny Liu
    TechNet Community Support

     
  • Thursday, March 21, 2013 6:53 AM
     
     
    Sign In to Vote
    0
    How do you perform an insert command? From within a stored procedure, then it should work.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Thursday, March 21, 2013 11:19 AM
     
     
    Sign In to Vote
    0

    I say a previous forum entry that said:

    ". If the procedure has dynamic sql , you should grant access to the tables as well."

    My insert procedure is created from dynamic SQL  using exec...

    GADOI

     
  • Friday, March 22, 2013 10:12 AM
     
     Answered
    Sign In to Vote
    0

    Hallo Gadoi,

    as Uri has written - it has to work if...

    - the user "account" gets dedicated access to the relations
    - or - if you don't had to grant access to the relations to a dedicated relation - the correspondig rights have been granted to the public database group in SQL 2000...

    You need to check it.
    If you use dynamic sql and execute it with EXEC or SP_EXECUTESQL this seems to be the only explanation for it.

    Tip: Grant appropriate privileges to the dbo-schema for the user or a database_role.
    In this case you don't need to grant privileges to each relation ...

    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)