Issue with SQL Stored procedure.

Question

I could see the below entries in the ULS log

02/08/2010 14:36:46.12 w3wp.exe (0x15F4) 0x18A0 CMS Publishing 8x0a High AppDomainUnloadListener.RegisterSelf() entered lock(this=15368010)

02/08/2010 14:37:25.59 w3wp.exe (0x15F4) 0x1744 Windows SharePoint Services Database 880i High System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.proc_GetDatabaseInformation'.

02/08/2010 14:37:32.53 w3wp.exe (0x15F4) 0x1744 Windows SharePoint Services Database 98rs High An error occured while trying to grant the user "X" access to the "Y" database on the SQL server. This may be expected if the caller does not have permission to perform this operation in sql server or if the database is read-only. To ensure that SharePoint functions correctly, manually grant this user access to the database before performing this operation. The user must also be granted access to the db_owner group in the database. The following information was provided with this error: User does not have permission to perform this action.

02/08/2010 14:37:32.53 w3wp.exe (0x15F4) 0x1744 Windows SharePoint Services Database 880i High System.Data.SqlClient.SqlException: User does not have permission to perform this action.

Any clues?

Answer 1

You have a permission denied issue. If you have permissions set on your database and you just added the proc 'dbo.proc_GetDatabaseInformation' you may need to give your users access to it. Specifically, any users that your website uses. Otherwise those users will not be able to call that proc and you will see the above error.

Answer 2

Whatever database user is calling this stored procedure will either need to have execute permissions for the stored procedure specifically or execute permissions in general. Your client program probably corresponds to one or more database users.

GRANT EXECUTE ON dbo.proc_GetDatabaseInformation TO [DatabaseUserName]

Answer 3

The error relating to dbo.proc_GetDatabaseInformation is a known issue with the Sharepoint 2007 infrastructure update - microsoft say you should ignore that error message: http://support.microsoft.com/kb/951695