with Execute as

Question

with Execute as

terry999

SSCertifiable

Points: 5033
More actions
June 1, 2018 at 7:31 am

#353724

Hi
Have an app that saves its internal reports (the SQL) in a table tblReports.
I would like to make these queries available to outside apps e.g. EXcel so I wrote a simplified version of SP:
create proc sp_RunSavedReport @ReportName as nvarchar(100) --WITH EXECUTE AS ???? as set nocount on DECLARE @SQL nvarchar(max) = '' SET @ReportName = RTRIM(ISNULL(@ReportName,''))
select @sql = [QuerySQL] from tblReports where ReportName = @ReportName -- Dynamic SQL exec sp_executesql @sql
I have a windows group with enough permissions but I cannot put
WITH EXECUTE AS [Domain\GroupName]
I could put
WITH EXECUTE AS [Domain\Bob.Smith]
who is a member of the group, not acceptable.
I could create an SQLlogin add to this DB and make it part of the roles datareader and denydatawriter. It would only be used for this SP.
Is there another way to have this SP run with permissions (datareader and denywritter ) for any user who executes it.?
It does allow
ALTER AUTHORIZATION ON OBJECT::sp_RunSavedReport TO [groupname];

But if I change the SP to EXECUTE AS OWNER
it fails

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Why can you not, instead, grant the AD Group Execute permissions on the object? Is the dynamic SQL calling objects owned by other users, or another database?

Also, are you storing said Dynamic SQL in a column in a table? That seems like a bad idea.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

terry999 SSCertifiable Points: 5033 More actions · Answer 2

Hi
The table does store SQL in one column. third party software cannot be changed.
The AD group contains Power users only, they are members of built in datareader role. They can run this SP with no problems.

I gave execute rights on this SP to ordinary users. The SP fails because ordinary users do not have rights to read tables the dynamic SQL might reference.

I want to allow ordinary users to be able to Only run queries that the App has saved in this table.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

The user calling the procedure must have impersonation rights on the EXECUTE AS principal (either login or database user). You may want to create a readonly Reporting login or user (you don't say whether the report queries are confined to a single database) and grant impersonate rights to that instead of to a power user login, then use that in the EXECUTE AS.

Thom A SSC Guru Points: 99126 More actions · Answer 4

terry999 - Friday, June 1, 2018 8:33 AM
Hi
The table does store SQL in one column. third party software cannot be changed.
The AD group contains Power users only, they are members of built in datareader role. They can run this SP with no problems.

I gave execute rights on this SP to ordinary users. The SP fails because ordinary users do not have rights to read tables the dynamic SQL might reference.
I want to allow ordinary users to be able to Only run queries that the App has saved in this table.

I think you needto talk to your third party vendor then. Storing dynamic SQL in a single column in a table is a TERRIBLE idea; if someone has the ability to UPDATE or INSERT into that table, that could do a lot of damage.

And just bceause a user doesn't have permission to SELECT from a table, doesn't mean they can't when they use the SP. If they have the same owner and are in the same database, then the permissions will be implied. You didn't say they didn't when I asked, so I assume they are? Thus, youdon'tneedto do anything more than grant them EXECUTE permissions on the SP.

For example, have a look at the below. Notice that SampleUser doesn't have SELECT permissions on SampleTable, but does have EXECUTE permissions on SampleProc. This means, when they execute SampleProc, it is successful:
USE Sandbox; GO CREATE USER [SampleUser] WITHOUT LOGIN; GO

CREATE TABLE dbo.SampleTable (SomeValue varchar(100)); INSERT INTO SampleTable VALUES('sdfkjhasfkjab'),('sdlijhgdsalfj'); GO

CREATE PROC dbo.SampleProc AS SELECT SomeValue FROM SampleTable; GO

GRANT EXECUTE ON OBJECT::dbo.SampleProc To SampleUser; GO

EXECUTE AS USER = 'SampleUser'; GO

SELECT * FROM SampleTable;--This will fail GO

EXEC SampleProc; --This works

GO REVERT; GO --Cleanup DROP USER SampleUser; DROP PROC SampleProc; DROP TABLE SampleTable; GO

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

terry999 SSCertifiable Points: 5033 More actions · Answer 5

Thanks
Yes the SP is in the same DB as the tables.

I wonder if somehow "delete from tbl" could find its way in the table. I would hope the App disallows that or even under what privileges the Queries run as.
As I'm just writing reports I've added all users including the Power users to the role deny_datawrite. The ordinary users can only get Data via my SPs, they cannot select from any tables/views/functions.

@thom-2 the difference here is, the SP is using dynamic SQL in your example its the equivalent of;
CREATE PROC dbo.SampleProc AS exec sp_executesql N'SELECT SomeValue FROM SampleTable' GO
This will fail : "The SELECT permission was denied on the object 'SampleTable'"

Thanks thom did not know creating a user without a login was possible. I did try doing this via the GUI but always wanted a login.
CREATE USER [SampleUser] WITHOUT LOGIN;

I will make use of that.

Thanks for taking the time to respond