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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 26, 2014 at 4:50 am

  #1761045

  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

  November 26, 2014 at 6:57 am

  #1761075

  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.