No Permissions To Execute SP That Rebuilds Indexes

Hi,

I have a stored procedure that rebuilds the indexes of my DB tables.

It works fine when I execute it from SSMS or my windows forms app but when I try and execute it from an asp.net webpage via the on load event it says "user does not have permissions to perform this action".

I have added execute permissions to the SP in the same way that I have done for all my other SPs that get executed via my website.

Any ideas?

Here is the SP & Code ...

////////////////////////////////////////////////////////////////////////////////////////
Dim connectionString As String = ConfigurationManager.ConnectionStrings("UsualConnectionString").ConnectionString

Using NewSQLConnection As New SqlConnection(connectionString)
NewSQLConnection.Open()
Dim oCmd As New SqlCommand(strSQL, NewSQLConnection)
oCmd.CommandType = Data.CommandType.Text
oCmd.CommandText = "sproc_WF_CheckDB_ShrinkDB_RebuildIndexes_UpdateStats"

'Try
oCmd.ExecuteNonQuery()
' ExecuteSQLStatement = True
'Catch
' ExecuteSQLStatement = False
'End Try
End Using
//////////////////////////////////////////////////////////////////////
ALTER PROCEDURE [dbo].[sproc_WF_CheckDB_ShrinkDB_RebuildIndexes_UpdateStats]
-- Add the parameters for the stored procedure here

AS
BEGIN

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

EXEC sp_UpdateStats

END
//////////////////////////////////////////////////////////////////////

Thanks in advance,

Will

 

Does your web application run with the same db credentials (uid/pwd) as you use with SSMS / your winform application? Have you tried commenting out the line in the sproc: EXEC sp_updatestats?