Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic SQL, Ownership Chaining, and EXECUTE AS Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 1:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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.
Post #1395858
Posted Thursday, December 13, 2012 12:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1396017
Posted Thursday, December 13, 2012 12:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 251, Visits: 581
See you can also try to give access permission on master and grant on sp_executesql..
Post #1396026
Posted Thursday, December 13, 2012 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1396275
Posted Thursday, December 13, 2012 11:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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?
Post #1396315
Posted Thursday, December 13, 2012 11:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1396325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse