Stored procedures are pre-compiled SQL routines that are stored on the database server. Some of the advantages of stored procedures include better performance (as they are pre-compiled) and reuse (since they are on the server they can be used by anyone with access).
Stored procedures on MySQL
We don't support the creation of stored procedures on our shared hosting environment for security reasons. If you require stored procedures, we recommend upgrading to a VPS subscription.
Stored procedures on MSSQL
By default, customers will get permission errors when attempting to use stored procedures in MSSQL, as they do not have DBO permissions (Database Owner). While we cannot supply DBO permissions to end users, we automatically set the db_securityadmin role to your database user, allowing access to running stored procedures upon creation of the user inside the Console.
Grant execute permission to a stored procedure
You can grant 'execute' permissions to a stored procedure using SQL or using the GUI interface for MSSQL.Using EMS SQL 2005 Manager Lite (download EMS SQL 2005 here), you can grant execute permission following these steps:
- Connect to Database
- Go to tools then [Grant Manager]
- Under object permissions tab, select [Procedures]
- Right click on a procedure and select [Grant On All]
- Press [commit] to run the query
Stored procedure to send CDOSYS mail
By default, the database user roles created will not allow clients to use CDOSYS mail as a stored procedure. Only members of the sysadmin fixed server role can run the OLE automation stored procedures. We can't give this type of role to a shared database user on the MSSQL server, therefore we recommend customers use CDO or W3JMail for mail instead.