Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql