stored procedure question

  • I have a database where I want people to load tables, but I don't want to grant Create Table. Instead I created a stored procedure that should take care of it, and I just want the users to execute the SP.

    I have given users : sp_grantdbaccess, db_datareader, db_datawriter, Execute

    What am I missing?

    Thanks a bunch

    SP:

    CREATE PROCEDURE SP_Transfer (@Server varchar(128),@Database varchar(128),@Table varchar(128))

    with execute as owner

    AS

    EXEC('SELECT top 0 *

    into Public.'+@Table+'

    FROM '+@server + '.' + @database + '.' + @table+';')

    EXEC('insert into Public.'+@Table+'

    select * from '+@server + '.' + @database + '.' + @table+';')

    GO

  • Does it throw an error, or just not do something that it's supposed to?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • you are missing the schema, aren't you ?


    * Noel

  • we get

    Msg 15274, Level 16, State 1, Line 1

    Access to the remote server is denied because the current security context is not trusted.

    I presume this is because of the 'With Execute as owner' and not having permissions to create a table.

  • good one, but schema is implicit with @table

    example

    exec SP_Transfer 'Server', 'DB', 'dbo.table'

  • you've answered the question to some degree. try using an account in sql server that has sufficient rights.

  • So you have linked servers to all possible values of "@server" ?

    If you do you need also map a remote logins so that this works.


    * Noel

  • Pieter (4/14/2009)


    Msg 15274, Level 16, State 1, Line 1

    Access to the remote server is denied because the current security context is not trusted.

    Gut feel is that's due to the linked server. Either the linked server security isn't set up correctly or the linked server security's using SQL authentication and the remote server's windows authentication (trusted) only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • we are setup for trusted connections only.

    The linked server does work when you issue "select * into ... from Server.db.dbo.table"

    current through is to put grant access/revoke around the SELECT into statement in the SP. Not pretty but appears somewhat functional

Viewing 9 posts - 1 through 8 (of 8 total)

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