User Granted Access to Stored Procedure but Can't Run Query

sql sql-server stored-procedures database-permissions grant
Kiril picture Kiril · Feb 5, 2009 · Viewed 9.5k times · Source

I am working on a product that runs an SQL server which allows some applications to login and their logins are granted permission to run a stored procedure- AND NOTHING ELSE. The stored procedure is owned by an admin; the stored procedure takes a query and executes it, then the results are returned to the application.

Unfortunately I can't figure out why the application can call the stored procedure to which it's granted access, but the stored procedure cannot execute the SQL statement which was passed into it.

The stored procedure executes the passed in query when I'm logged in as an admin, but when I log in as the limited user it throws an exception in the execute statement.

For example:

EXEC [Admin].[STORED_PROC] @SQL_STATEMENT = 'SELECT * FROM table_x'

the STORED_PROC looks something like this:

BEGIN TRY
   EXEC (@SQL_STATEMENT)
END TRY
BEGIN CATCH
   -- some logging when an exception is caught, and the exception is caught here!!!
END CATCH

There is nothing inside the the try catch statement except that EXEC... and the SQL_STATEMENT works when I'm logged in as the Admin, but not when I'm logged in as the User.

Can anybody help me figure out what permissions I need to set in order to allow the User to run queries through the stored proc only?

So there have been some comments about allowing raw SQL statements to be executed via stored proc defeats the purpose of using a stored proc... but in reality what we're actually doing is we're passing an encrypted SQL statement into the stored proc and the stored proc gets the statement decrypted and THEN it executes it.

So yes, in reality raw SQL statements are not secure and they defeat the purpose of stored procs, but I don't know how to encrypt SQL queries that are passed through ODBC and run against a pre-2005 SQL Server.

In any case, I tried to put up some minimal safeguards to at least have some basic security.

Answer

DJ. picture DJ. · Feb 5, 2009

Since you are using dynamic sql, SQL server can't tell which tables you are using, so you have to grant SELECT rights to all the tables as well

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More