How can I tell why role access not sufficient?

• glynbach

  SSC Veteran

  Points: 285

  More actions

  January 13, 2005 at 4:11 am

  #92097

  Hi

  I have a procedure I am calling that I know needs sysadmin access. I have added sysadmin access to the database user that is used by the application calling the procedure. If I try and run it from query analyser, logged in as this database user, using sql server authentication, it runs fine. In runtime it does not seem to have the permissions to complete the task.

  I am having a couple of difficulties testing this issue. Firstly I am not sure being logged in as the user in query analyser is accurate as whether I turn sysadmin access on or off it still works. Is there any way I can find out at runtime what permissions the user has?

  The procedure I am trying to run is below. Any help would be greatly appreciated.

  CREATE procedure sp_SMTPMail

   

  @From varchar(100),

  @To varchar(100),

  @Subject varchar(100),

  @Body varchar(4000),

  @cc varchar(100) = null,

  @BCC varchar(100) = null

  as

   

  insert into bb_debug (debugText) values ('User='+SYSTEM_USER);

  -- try to create table just to check user is a sys admin

  create table master.dbo.yoyo (yoyo varchar(1));

  begin

   declare @MailID int

   declare @hr int

   EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT

  insert into bb_debug (debugText) values ('@hr='+ltrim(rtrim(str(@hr))));

   EXEC @hr = sp_OASetProperty @MailID, 'From',@From

   EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body

   EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC

   EXEC @hr = sp_OASetProperty @MailID, 'CC', @cc

   EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject

   EXEC @hr = sp_OASetProperty @MailID, 'To', @To

   EXEC @hr = sp_OAMethod @MailID, 'Send', NULL

   if @hr <> 0

   begin

    RAISERROR('sp_SMTPMail failed to send email to %s with content %s', 16, 1, @To, @Body) with LOG

   end

   EXEC @hr = sp_OADestroy @MailID

  end

  GO

  Thanks

  Glyn

• vadba

  SSChampion

  Points: 11133

  More actions

  January 14, 2005 at 10:10 am

  #537508

  You can test whether the current user is a member of the sysadmin role using the IS_SRVROLEMEMBER() function.

  IF IS_SRVROLEMEMBER('sysadmin') = 0  -- =1 if a member of role

  BEGIN

    RAISERROR('Cannot execute sp_SMTPMail - user does not have the necessary privledges', 16, 1)

    RETURN

  END

   

• glynbach

  SSC Veteran

  Points: 285

  More actions

  January 17, 2005 at 3:47 am

  #537631

  Thanks for your help.