December 12, 2012 at 1:00 pm
Hi all,
I've got a procedure which is using dynamic SQL to construct an SQL statement, and then execute the statement using sp_ExecuteSQL.
I've given permission to the login to execute the stored procedure - but, since I'm using sp_ExecuteSQL, it is failing, due to the fact that the login does not have permissions on the underlying table.
As far as my research has told me, there are two solutions to the problem:
1) Grant the user permissions to each underlying table. This means SELECT, and possibly INSERT AND DELETE depending on what the dynamic SQL is supposed to do.
2) Create the procedure with EXECUTE AS, and use a different login that has permissions on the underlying tables.
Seems to me like option #2 is the better choice, but I'd like to know if there are any risks associated. It seems to me that there shouldn't - all the login has permission to do is execute the stored procedure that I've given it access to.
December 13, 2012 at 12:38 am
I use option 2 all the time using a login-less Database User. This way the procedure executes as a Database User with elevated permissions but no one can ever connect to the database as that user directly meaning it only exists for purposes of running dynamic SQL. This technique only works however if you are only making calls to tables within the same database, i.e. not doing any cross-database calls in your Dynamic SQL.
USE [YourDatabase];
GO
CREATE USER [DynamicSqlRunner] WITHOUT LOGIN;
GO
CREATE PROC dbo.WhateverProc (@ParameterName VARCHAR(100))
WITH EXECUTE AS 'DynamicSqlRunner'
AS
BEGIN
-- proc code goes here
SELECT 1
END
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 13, 2012 at 12:49 am
See you can also try to give access permission on master and grant on sp_executesql..
December 13, 2012 at 9:48 am
jeetsingh.cs (12/13/2012)
See you can also try to give access permission on master and grant on sp_executesql..
That is not likely to help. All server logins automatically get a database user created for them in master and all users in master have execute permissions on sp_executesql. The issue is likely not the lack of permissions to execute sp_executesql itself, it's likely the lack of permissions of the user in the user-database that is executing the user-proc that calls sp_executesql to access the resources used by the dynamic sql submitted to sp_executesql.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 13, 2012 at 11:04 am
Interesting - so for cross-database calls, this approach will not work? In which case, what will need to be done - the calling login will have to have access to the tables on the other database? Or will it just be the EXECUTE AS login that will need access to the tables on the other database?
December 13, 2012 at 11:19 am
kramaswamy (12/13/2012)
Interesting - so for cross-database calls, this approach will not work? In which case, what will need to be done - the calling login will have to have access to the tables on the other database? Or will it just be the EXECUTE AS login that will need access to the tables on the other database?
If you try to use EXECUTE AS LOGIN you start running into impersonation issues. The cleanest way to support that in my opinion is to sign the module with a certificate linked to a login that has access to do everything required in the instance, i.e. what is required across various databases.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply