Dynamic SQL, Ownership Chaining, and EXECUTE AS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Dynamic SQL, Ownership Chaining, and EXECUTE...

Dynamic SQL, Ownership Chaining, and EXECUTE AS

Rate Topic

Display Mode

Topic Options

Author

Message

kramaswamy

Posted Wednesday, December 12, 2012 1:00 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:49 PM
Points: 785, Visits: 1,536

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

opc.three

Posted Thursday, December 13, 2012 12:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1396017

jeetsingh.cs

Posted Thursday, December 13, 2012 12:49 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 192, Visits: 407

See you can also try to give access permission on master and grant on sp_executesql..

Post #1396026

opc.three

Posted Thursday, December 13, 2012 9:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1396275

kramaswamy

Posted Thursday, December 13, 2012 11:04 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:49 PM
Points: 785, Visits: 1,536

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

opc.three

Posted Thursday, December 13, 2012 11:19 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

kramaswamy (12/13/2012)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1396325

« Prev Topic | Next Topic »

Permissions