"Execute as" failing when it shouldn't

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

"Execute as" failing when it shouldn't

Author

Topic

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-02 : 08:58:31

Hello all
I am working with SQL 2012 and trying to get a secure link from SSRS to stored procedures which in turn access tables. Basically I have SSRS connecting to SQL via a SQL user (say SSRSToSQL). This user has rights to execute relevant stored procedures ONLY. Inside the stored procedure is an "execute as login = 'SQLReadOnly' which is an account with read only access to most databases.

Using management studio I log in as SSRSToSQL. I can then successfully run the following lines but only one by one. If I highlight all three and run them I get "Login failed for user 'SSRSToSQL'"

execute as LOGIN='SQLReadOnly';
select top 100 * from MyLinkedServer.MyDatabase.dbo.MyTable;
revert

If I put those three lines into a stored procedure and execute it as SSRSToSQL it fails stating, quite rightly, that SSRSToSQL can't access MyTable. It is like it is ignoring the "execute as" statement as it did with the three lines above when run together.

Is there something which checks the securities before running? If so it could be jumping to the wrong conclusion that the SP won't run under current auths rather than seeing that the data will be retrieved under a different login.

There is a forehead shaped dent in my desk over this one so any help gratefully received!

Alex Stennett

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 10:07:34

try


execute as LOGIN='SQLReadOnly'
GO
select top 100 * from MyLinkedServer.MyDatabase.dbo.MyTable
go
revert
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-02 : 10:41:06

That works fine in management studio as it batches them all up. Won't work in a stored procedure though

Alex Stennett

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 10:58:05

try wrapping them inside exec and execute


EXEC('execute as LOGIN='SQLReadOnly'
GO
select top 100 * from MyLinkedServer.MyDatabase.dbo.MyTable
go
revert
go')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 11:14:44

quote:
Originally posted by visakh16

try wrapping them inside exec and execute

EXEC('execute as LOGIN=''SQLReadOnly''
GO
select top 100 * from MyLinkedServer.MyDatabase.dbo.MyTable
go
revert
go')

Need to double up the single quotes that are imbedded in a string to be EXEC'd

Be One with the Optimizer
TG

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-02 : 11:46:21

No joy
Still doesn't like the GO and the stored procedure is big enough to make this a pain.

tried
EXEC('execute as LOGIN=''SQLReadOnly''')

EXEC('select top 100 * from MyLinkedServer.MyDatabase.dbo.MyTable')

EXEC('revert')

but the execute as seems to run only in the EXEC() and is lost when you come back out.

Alex Stennett

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-02 : 11:56:28

Bottom line is that the code is correct but SQL is wrongly assessing it to be otherwise. Is there a way of telling it to stop fussing and just run the SQL?

Alex Stennett

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 12:07:24

No way to have a GO inside stored procedure code. Can you have the entire SP run as your login (SQLReadOnly)? If so you might try the EXECTE AS clause in the creation of the SP:

CREATE PROCEDURE mySP
WITH EXECUTE AS 'SQLReadOnly'
AS

Be One with the Optimizer
TG

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-03 : 03:20:10

Yes that is the way I am trying to work it but it still fails referring to the "external" login rather than the "execute as" login. It just seems to evaluate the contents of the stored procedure against the curtrent login rather than the one I am telling it to use inside the SP and hence it fails. Seriously frustrating!

Alex Stennett

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-12-03 : 04:28:36

Just as an aside (as the code from Tara should work fine), the user you are using to create the SP does have IMPERSONATE and CONTROL permissions, doesn't it?

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-03 : 06:07:06

OK I have had to revert to using the execute( . . . ) method which is a shame. Heck of a mess when using temp tables and "if" splits and that is with a fairly basic SP.
Ah well

Alex Stennett

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-03 : 09:59:44

Have you considered replicating the remote tables to the local instance so you have everything together?

Be One with the Optimizer
TG

DrAardvark
Starting Member

7 Posts

Posted - 2013-12-05 : 11:09:03

I've changed tack with this and am using certificate signed stored procedures. Seems fine so far ...........
Alex

Alex Stennett

Kristen
Test

22859 Posts

Posted - 2013-12-05 : 11:49:03

That's the way we went when writing Sprocs that needed to do dynamic (SELECT only) SQL on tables, and we wanted to only give EXECUTE permission to users, not SELECT on all tables ...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-05 : 11:59:01

I like the signed SP / certificate solution as well.
It is a little funky to implement but for Kristen's case and/or if you have SPs that reference tables in other DBs it is a nice solution.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources