SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 "Execute as" failing when it shouldn't
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/02/2013 :  08:58:31  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 12/02/2013 :  10:07:34  Show Profile  Reply with Quote
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
Go to Top of Page

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/02/2013 :  10:41:06  Show Profile  Reply with Quote
That works fine in management studio as it batches them all up. Won't work in a stored procedure though


Alex Stennett
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/02/2013 :  10:58:05  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5907 Posts

Posted - 12/02/2013 :  11:14:44  Show Profile  Reply with Quote
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

Edited by - TG on 12/02/2013 11:17:25
Go to Top of Page

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/02/2013 :  11:46:21  Show Profile  Reply with Quote
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
Go to Top of Page

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/02/2013 :  11:56:28  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5907 Posts

Posted - 12/02/2013 :  12:07:24  Show Profile  Reply with Quote
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
Go to Top of Page

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/03/2013 :  03:20:10  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3603 Posts

Posted - 12/03/2013 :  04:28:36  Show Profile  Reply with Quote
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?

Go to Top of Page

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/03/2013 :  06:07:06  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5907 Posts

Posted - 12/03/2013 :  09:59:44  Show Profile  Reply with Quote
Have you considered replicating the remote tables to the local instance so you have everything together?

Be One with the Optimizer
TG
Go to Top of Page

DrAardvark
Starting Member

United Kingdom
7 Posts

Posted - 12/05/2013 :  11:09:03  Show Profile  Reply with Quote
I've changed tack with this and am using certificate signed stored procedures. Seems fine so far ...........
Alex

Alex Stennett
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/05/2013 :  11:49:03  Show Profile  Reply with Quote
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 ...
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5907 Posts

Posted - 12/05/2013 :  11:59:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000