Executing stored procedure as different user

Question

Executing stored procedure as different user

wim sturkenboom

SSC Veteran

Points: 288
More actions
November 26, 2014 at 4:45 am

#298848

I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @pTablename
END
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, please advise how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not help. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
Thanks for reading and thanks in advance for any advise.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Adding WITH EXECUTE AS OWNER means the contents of that procedure (and only the contents of that procedure) runs as the procedure's owner, probably dbo.

The permission missing is the VIEW DEFINITION permission, easiest to assign it on a schema to whatever user should be running that type of query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wim sturkenboom SSC Veteran Points: 288 More actions · Answer 2

GilaMonster (11/26/2014)
Adding WITH EXECUTE AS OWNER means the contents of that procedure (and only the contents of that procedure) runs as the procedure's owner, probably dbo.

Thanks, in that case I will keep it like that.

I will look into the VIEW DEFINITION when I have time; a first attempt threw an error in the line of 'the object is not compatible'. Will get back with more information if needed.