July 12, 2009 at 1:52 am
Dear All,
I need to pass a column name as a variable in an insert steatmen within a stored procedure.
I made this:
ALTER PROCEDURE [dbo].[InsertWithBoth]
-- Add the parameters for the stored procedure here
@FIELDS nvarchar(100),
@VALUES nvarchar(100),
@LANG nvarchar(100),
@DATETIME nvarchar(50),
@SNUM nvarchar(50),
@WNUM nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @SQL nvarchar(500)
set @SQL='insert into Books ('+@FIELDS+',FileLang,UploadDate,SNum,WNum)values ('+@VALUES+','''+@LANG+''','''+@DATETIME+''','+@SNUM+','+@WNUM+')'
exec(@SQL)
END
see the @FIELDS ??
after letting the application has the execute permission only on the db, this error occured:
INSERT permission denied on object TableName, database DatabaseName, schema SchemaName
any one has any suggestion???
Thanks in advance.
July 12, 2009 at 4:00 am
create table #temp1
(
fld1 varchar(10),
fld2 varchar(10)
)
declare @SQL nvarchar(500)
declare @FIELDS nvarchar(500)
declare @values nvarchar(500)
set @fields='fld1'
set @values='test value'
set @SQL='insert into #temp1 ('+@FIELDS+') values ('''+@VALUES+''')'
--print @sql
exec(@SQL)
This code worked fine for me. Can you post the table structure with sample data for your case?
see THIS for help.
July 12, 2009 at 4:02 am
The error you posted looks like the user doesn't have sufficient privilages to insert data in Books table. Can you reconfirm that as well?
July 12, 2009 at 4:13 am
Thanks For Your Reply,
The problem is that The user cant run the EXEC() nor the sp_executesql.
The User can only run any storedprocedure which can only make select,delete,insert or update.
He cant directly do any thing with any table.
I solved it by adding all the fields, without using the variable. It will take more time and code to know which are the filled Fields but i have 2 finish it. So Thank you very very much Pradeep Singh for your help
🙂
July 12, 2009 at 7:36 am
eslam.amgad
The User can only run any storedprocedure which can only make select,delete,insert or update
Have you considered utilizing the T-SQL statement Execute AS ....?
From Books On Line
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/613b8271-7f7d-4378-b7a2-5a7698551dbd.htm
Sets the execution context of a session.
By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement. In essence, the user or login account is impersonated for the duration of the session or module execution, or the context switch is explicitly reverted. For more information about execution context, see Understanding Execution Context. For more information about context switching, see Understanding Context Switching.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply