How to cut off error message when inserting data.

• Roust_m

  SSCoach

  Points: 17410

  More actions

  December 24, 2003 at 12:24 am

  #81758

  Hi!

  I am using this instruction to write all permissions of all users/roles into a table:

  INSERT #u EXEC sp_helprotect @username=@UserName

  Some users/roles do not have any explicit permissions in database, so sp_helprotect returns:

  Smith
  
  Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346 
  
  There are no matching rows on which to report. 

  This causes errors when inserting data into the table.

  When I try to use:

  EXEC sp_helprotect @username=@UserName 
  
  IF  @@ERROR...

  this doesn't help, cause

  EXEC sp_helprotect @username=@UserName 
  
  PRINT @@ERROR

  always returns:

  ... 
  
  ... 
  
  0 

  or

  Smith
  
  Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346 
  
  There are no matching rows on which to report.  
  
  0
  
  

  So no matter if the user has permissions or has not...

  How can I handle this error?

  Thanks.

  Edited by - Roust_m on 12/24/2003 12:28:32 AM

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 24, 2003 at 3:39 am

  #487719

  DECLARE @rval int 
  
  EXEC @rval = sp_helprotect @username=@UserName 
  
   

  @rval will be 0 if worked otherwise 1

  Far away is close at hand in the images of elsewhere.
  Anon.