How to Identify Permissions for SQL Server Tables & Stored Proc. via VB Code

[Brad H McCollum]

I'm writing an application using VB 6.0 as the front-end GUI, and the
MSDE version of SQL Server as the back-end (it's a program for a
really small # of users --- less then 3-4).

I'm trying to determine, through the Visual Basic interface, the
permissions of each user that's using the application on his/her
machine.

For example, let's say I'm user "Michael" that's sitting down at my
machine using the app. I've written. The security for logging into
SQL Server will be setup using Windows Security (Trusted Connection)
as opposed to Windows & SQL Server security. When Michael accesses a
particular form in the VB 6.0 GUI, I want to run some code that
automatically checks Michael's permission levels on the underlying
table (actually, a stored procedure supplying the data from the table)
that supplies the data to the form he's looking at and then give him
some feedback on the form as to what type of permissions he has while
he's browsing through the data shown in the form.

For example, Michael opens a particular form, code in the background
is run to identify that this is Michael accessing the form, the code
returns a value that identifies what type of permissions he has on the
data in the form, and a text box on the form informs Michael (for
example) that he only has read-only permissions to the data he is
viewing and cannot edit any of the data.

As another example, user Karen sits down at her computer, logs into
the application, opens the same form that Michael just opened, the
code is run in VB to detect the level of permissions she has on the
data being displayed in the form, and the text box on the form informs
her that she has editing permissions on the data in the underlying
table.

Etc...

If anyone can post an example of the code they use in accomplishing
this task in an application they've written, I'd really appreciate a
point in the right direction or a real-world example that's been
implemented by one of you. I've written several apps. thus far using
MSDE as the back-end, but the previous apps. I've written were for
clients that didn't care about restricting access to the
application... everyone could pretty much use the application as they
desired and do anything they desired to the data.

The current client I'm writing the app. mentioned here for wants to
have security in place to where various users access the application
with various levels of permissions to do stuff (or *not* do stuff) to
the data in the application.

Thanks very much in advance for any assistance / code provided!

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

[Simon Hayes]

"Brad H McCollum" <bm******@midsouth.rr.com> wrote in message
news:52**************************@posting.google.c om...

I'm writing an application using VB 6.0 as the front-end GUI, and the
MSDE version of SQL Server as the back-end (it's a program for a
really small # of users --- less then 3-4).

I'm trying to determine, through the Visual Basic interface, the
permissions of each user that's using the application on his/her
machine.

For example, let's say I'm user "Michael" that's sitting down at my
machine using the app. I've written. The security for logging into
SQL Server will be setup using Windows Security (Trusted Connection)
as opposed to Windows & SQL Server security. When Michael accesses a
particular form in the VB 6.0 GUI, I want to run some code that
automatically checks Michael's permission levels on the underlying
table (actually, a stored procedure supplying the data from the table)
that supplies the data to the form he's looking at and then give him
some feedback on the form as to what type of permissions he has while
he's browsing through the data shown in the form.

For example, Michael opens a particular form, code in the background
is run to identify that this is Michael accessing the form, the code
returns a value that identifies what type of permissions he has on the
data in the form, and a text box on the form informs Michael (for
example) that he only has read-only permissions to the data he is
viewing and cannot edit any of the data.

As another example, user Karen sits down at her computer, logs into
the application, opens the same form that Michael just opened, the
code is run in VB to detect the level of permissions she has on the
data being displayed in the form, and the text box on the form informs
her that she has editing permissions on the data in the underlying
table.

Etc...

If anyone can post an example of the code they use in accomplishing
this task in an application they've written, I'd really appreciate a
point in the right direction or a real-world example that's been
implemented by one of you. I've written several apps. thus far using
MSDE as the back-end, but the previous apps. I've written were for
clients that didn't care about restricting access to the
application... everyone could pretty much use the application as they
desired and do anything they desired to the data.

The current client I'm writing the app. mentioned here for wants to
have security in place to where various users access the application
with various levels of permissions to do stuff (or *not* do stuff) to
the data in the application.

Thanks very much in advance for any assistance / code provided!

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

You're probably looking for the PERMISSIONS() function, which shows the
current user's effective permissions on an object. To identify a user's
login, use SYSTEM_USER or SUSER_SNAME().

You should also check out roles, and instead of granting permissions to each
user, grant them to the roles instead. You can then also use IS_MEMBER() to
see if a user is in a specific role, and allow or deny actions based on
that. It may seem a bit pointless to create a role with only one or two
users, but adding and removing users from roles is much easier than granting
and revoking permissions on multiple objects.

You can also consider using stored procedures as much as possible, instead
of granting any permissions on tables, views etc. This means that users have
no access to base tables, so it's harder for them to do something they
shouldn't, accidentally or otherwise. In addition, it's easier to implement
complex more security logic in a procedure, where you can check functions
like the ones above, or perhaps even use your own permissions lookup table
if you need very specific permissions.

You might find this information useful (the second link shows how to display
only a particular user's data from a table which contains data for all
users):

http://vyaskn.tripod.com/sql_server_..._practices.htm
http://vyaskn.tripod.com/row_level_s..._databases.htm

And of course the "Managing Security" section in Books Online.

Simon