Still Trying to Figure Out Identification of SQL Server Stored Proc. Permissions via VB

[Brad H McCollum]

I've looked through many suggestions and partial examples all over
this newsgroup and still am not coming up with anything that does
specifically what I'm wanting to accomplish.

I'm writing a VB 6.0 application which uses SQL Server as the
back-end.

Here's an example of what I'm wanting to do...

A user accessing the VB GUI attempts to open a certain form. Code
runs behind the scenes in VB that checks the user's "Execute"
permissions on the stored procedure that retrives the data into the
form. If the user is determined to have "Execute" permissions to that
stored procedure, the user is allowed to proceed with opening the
form. If they don't have permissions, they are informed of this and
aren't allowed to open the form and view the data.

Let's say that we initially determine through the VB code that the
user has the ability to "Execute" the stored procedure that allows for
viewing of the data. We go ahead and let them open the form. Once
the form is being opened though, we run VB code to check for their
permissions on other stored procedure that can be used in UPDATING /
INSERTING / DELETING the records being displayed in the form. If they
don't have "Execute" permissions on these particular
Update/Insert/Delete stored procedures, then I'll have a text box on
the form show as visible to advise them while they're looking at this
data that they can't do anything to this data other than view it...
they can't insert new records, they can't delete any records, and they
can't update any records.

I have yet to find specific code that tells me how to check
permissions on a storedc procedure for the user that's logged-in, what
the resulting codes (i.e. 12291, etc.) mean as far as identification
of their permissions, etc.

If anyone can help me out here, I'd greatly appreciate it. I'd hate
to just have to resort to trying to allow the user to run the stored
procedures and just trap the error codes that may arise once each
stored procedure is executed and inform the user on the tail-end of
the process that they can't run the stored procedure. I'd like to
identify their permissions to the stored procedures on the front-end
of the process either before each form is opened or just as it's
opening to advise them early as to what they can/can't do with the
data being displayed in the form.

Thanks in advance for any examples/information!

Sincerely,
Brad H. McCollum
bm******@midsouth.rr.com

[Erland Sommarskog]

[posted and mailed, posted and mailed]

Brad H McCollum (bm******@midsouth.rr.com) writes:

I have yet to find specific code that tells me how to check
permissions on a storedc procedure for the user that's logged-in, what
the resulting codes (i.e. 12291, etc.) mean as far as identification
of their permissions, etc.

If the expresion:

permissions(object_id('dbo.yourprocedure')) & 0x20

returns a non-zero value, the user has permission to execute the procedure,
else not.

You can read more about the Permissions() function Books Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp