stored procedure question

Question

stored procedure question

Pieter-423357

SSCommitted

Points: 1619
More actions
April 14, 2009 at 12:58 pm

#130115

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

noeld SSC Guru Points: 96590 More actions · Answer 2

you are missing the schema, aren't you ?

* Noel

Pieter-423357 SSCommitted Points: 1619 More actions · Answer 3

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.

Pieter-423357 SSCommitted Points: 1619 More actions · Answer 4

good one, but schema is implicit with @table

example

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

Rajib Bahar Ten Centuries Points: 1007 More actions · Answer 5

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

Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql

noeld SSC Guru Points: 96590 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Pieter-423357 SSCommitted Points: 1619 More actions · Answer 8

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