April 14, 2009 at 12:58 pm
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
April 14, 2009 at 1:02 pm
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
April 14, 2009 at 1:11 pm
you are missing the schema, aren't you ?
* Noel
April 14, 2009 at 1:11 pm
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.
April 14, 2009 at 1:13 pm
good one, but schema is implicit with @table
example
exec SP_Transfer 'Server', 'DB', 'dbo.table'
April 14, 2009 at 1:14 pm
you've answered the question to some degree. try using an account in sql server that has sufficient rights.
April 14, 2009 at 1:15 pm
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
April 14, 2009 at 1:21 pm
Pieter (4/14/2009)
Msg 15274, Level 16, State 1, Line 1Access 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
April 14, 2009 at 1:44 pm
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