I'm working with an SP that generates a SELECT stmt with different WHERE clauses based upon up to 5 parameters supplied to the SP from the application. The statement is created in a variable then EXECuted from within the SP. The application is receiveing a "Select Permission Denied" error on the various tables involved when attempting to run the SP. I granted SELECT permissons to the id running the SP but that's not what I want to do. I only want to allow EXEC permissons for the SP.
Is there a way to code the SQL stmt to accept any combination of the parameters and still not have to perform the additional EXEC on a constructed SQL stmt?
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
In SQL 2005, is this an acceptable (prefered) way to give an application account EXEC permissions for sprocs and funcs in a specific database?
CREATE ROLE db_executor GRANT EXECUTE TO db_executor
And then of course assign my user to this role on the database level.
I am trying to get away from adding exec to every sproc "manually" and then of course also having to add exec for any new sprocs that get added into the database.
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables. All objects are dbo owned and execute permission has been given to all users. Can ant one help? Rob
When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"... Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
declare @TableName Varchar(255);--Just For Testing---DELETE!! declare @Filename varchar(255); --Store Distinct filename declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units declare @SumValue Varchar(255); Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName Set @SumUnits = 'select sum(Units) from ' + @TableName Set @SumValue = 'Select sum(Value) from ' + @TableName
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate, UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
is it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?
i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.
I found this statement in BOL and it didn't make it to work.Is anybody out there who ha the same problem? Database is MASTER. USE master EXEC ("USE pubs") SELECT * FROM authors
Running this dynamic sql construct gives me an error because somehow it does not accept my variable @table or it is recognised differently. If run directly no problem but apparently the single quotes are a problem.
Print @Table (db and table name: opms..transactions) Select @sql = 'Select * From Payments where not exists (Select * from Hist Where TableName = ' + @Table + ' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')' Print @sql EXEC (@sql)
opms..Transactions Select * From Payments where not exists (Select * from Hist Where TableName = opms..Transactions and sYear = 2003 and sMonth = 12)
Server: Msg 1004, Level 15, State 1, Line 1 Invalid column prefix 'opms.': No table name specified
I am seeking an expert help for the following issue, please find the code am using first ...the problem mentioned below that... ---------------------------------------------- DECLARE ,@DBName VARCHAR(128) ,@LoginName VARCHAR(128) ,@SQL VARCHAR(2000)
EXEC(@SQL) IF @@ERROR <> 0 PRINT @@ERROR ELSE BEGIN PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +'] PRINT @@ERROR END --------------------------------------------------------------
Suppose I am trying to REVOKE a database access which not exist iw will give me a mesage like , Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36 User 'Loginname' does not exist in the current database.
But the @@ERROR will return 0 as it was a successfull execution of EXEC(@SQL) .
So How can I retrieve the error value 15008 in a variable ..?
I'm trying to do something like the following, but it keeps complaining that I need to declare @max, even though I have (and it is of the same type as link_id).
EXEC('SELECT @max=MAX(link_id) FROM '+ @str1)
I've looked into sp_executesql but I'm not entirely sure how that functions. Any suggestions?
I have a stored proc that assigns a value to a field based on user input from an Access front end.
The last part of the stored proc sends an email if certain conditions are met.
It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?
The users are getting this message:
EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)
I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:
Code is really not important. Any sys SP can replace the above code.
I am getting the following error Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80 An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue, http://www.sommarskog.se/share_data.html But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------ I think, therefore I am - Rene Descartes
How to exec the following function from my select statement and get my return value:-
ALTER FUNCTION [dbo].[fn_qty] (@qty decimal (10,4), @price decimal (10,4), @pieces int, @mpt int=0)
returns decimal (10,4)
AS begin declare @totamt decimal (10,4) if @pieces = 0 begin SET @totamt = (@QTY * @price) end else begin SELECT @mpt = (SELECT case mpq when 1 then 10 when 2 then 100 else 100 end FROM ims.parm) SET @totamt=(((FLOOR(@QTY)*@PRICE))+(((@qty-FLOOR(@QTY))* @mpt)/@pieces)*@price) end return @totamt end
I can exec the function as exec command as follows:- exec @totamt( erpinv.dbo.fn_qty 3.5,6,24 )
Hello, I am very, very new to the world of SSIS and from a totally different platform (iSeries), learning, digging and finding golden nuggets as I go.
Two questions please.
Question One:
I am presently encountering the following exception.
SSIS package "DW.CUST_CNCL_ORD.dtsx" starting. Error: 0xC00291E2 at BP Parter Exec SQL, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset". Task failed: BP Parter Exec SQL Warning: 0x80019002 at DW CUST_CNCL_ORD: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "DW.CUST_CNCL_ORD.dtsx" finished: Failure.
Try as I do to find information regarding this exception, I am not able to. Can anyone share their wisdom on this topic?
Question two:
Is there a book that you may recommend that will contribute to my learning this new platform and evironment? I presently have the SQL Server 2005 integration services book.
Thank you in advance for any information you may provide.
