1

I'm going trying to fix an issue that is burning and I've spent the last day without success. I have a database server running SQL Server 2008, one database and one stored procedure.

The stored procedure logic contains the following sentence: 

IF EXISTS
(
 SELECT * 
 FROM dbo.sysobjects 
 WHERE id = OBJECT_ID( '<StoredProcedureNameHere>' ) 
 AND OBJECTPROPERTY( id, N'IsProcedure' ) = 1
)
BEGIN
   -- Do something here
END

Now, the issue:

If I run a query from the Management Studio script to execute the stored procedure under investigation it works correct and executes the "-- Do something here" logic. However, exactly the same stored procedure invoked from a web service does NOT work. I disabled for testing the entire IF EXISTS(---) block and retrieved information is the correct one.

My preliminary conclusion is there should be any permission setting ( I'm obviously not aware of ) configured for my user account over the database (and thus on sysobject table ) allowing me to successfully get the information that is NOT in place of the user account used by the web service to access the database.

Anybody knows any standard setting I should validate to have this working ?.

Hint - Both database and web service components were perfectly working. All this kind of issues started to come up right after the database server crashed and Operations teams needed to rebuild the database server. On asking about the state of the databases, they said they were able to backup the database and they must work as restored right after the new server was build.

Any assistance will be greatly appreciated.

Thanks,

G21
  • 1,297
  • 2
  • 18
  • 39
  • 1
    Did you actually check the permissions for the account used by thw webservice? – Dan Bracuk Feb 13 '14 at 17:26
  • Thats a bit weird...I thought storedprocs run under their own permission (SQLAgent?), therefore should execute the same regardless of what account executes it. My only thought is specify the full table name server.msdb.dbo.sysobjects - I'll have to follow the question, curious what the answer here is – Twelfth Feb 13 '14 at 17:33
  • 1) the web service may specify a connection string in the web.config - or if not the connection string should be in the application somewhere. 2) if a username/password is not specified in the connection string, (SSPI) then its using the Windows user that runs the web service to connect, in which case you need to look into IIS – Tom Brown Feb 13 '14 at 22:28
  • Stored Procs will run under the id of the logged in user. It could be. 1) the web service: a connection string in the web.config - or if not the connection string should be in the application somewhere. 2) if a user/pwd is not specified in the connection string, (SSPI) then its using the Windows user that runs the web service to connect, if so you need to look into IIS, and find out which user runs the application pool. This can get messy as new versions of IIS use application pool identities - which don't show up as real users but its still possible to give them permissions in the database. – Tom Brown Feb 13 '14 at 22:35

1 Answers1

0

Fortunatelly the issue was cleared out after Operations Team figured out the configuration for the domain account being used by the web service was incorrect. Couple of days looking for something that should not be an issue if the Config wiki page would be followed up step by step (everybody complains when there is no docu, but when it is available there is not being used at all ).

Anyway,if you had the same issue make sure to check the user info has enough privilegies to execute the stored procedure.

Hope this helps somebody else.

Thanks,

G21
  • 1,297
  • 2
  • 18
  • 39