How to Identify a User's Permissions to SQL Server Tables via VB 6.0 Application

From: Brad H McCollum (bmccoll1_at_midsouth.rr.com)
Date: 10/13/04 

Date: 13 Oct 2004 11:14:14 -0700

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
bmccoll1@midsouth.rr.com

Relevant Pages

  • Re: Public Role for SQL 2000
    ... public is the default role for all database users. ... their minimum permissions and privileges. ... Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant: ...
    (microsoft.public.sqlserver.security)
  • Re: How to Identify a Users Permissions to SQL Server Tables via VB 6.0 Application
    ... > SQL Server will be setup using Windows Security ... > some feedback on the form as to what type of permissions he has while ... > For example, Michael opens a particular form, code in the background ...
    (microsoft.public.sqlserver.security)
  • Roles based security 2005 versus 2000
    ... There is something really easy and simple that I do to control permissions in Sql Server 2000: this is role based security. ...
    (microsoft.public.sqlserver.security)
  • Re: Configuring SQL 2000 for use with ESRI ArcSDE
    ... You can assign SQL Server users to database roles using sp_addrolemember: ... Permissions are entirely application specific. ... vendor for clarification of security requirements. ... I have Two databases 3 Roles ...
    (microsoft.public.sqlserver.server)
  • RE: What server hardening are you doing these days?
    ... permissions on their data, and Microsoft encourages ISVs to minimize ... I've been able to discuss ACLs and other security issues in Windows with ... Control or DAC (which is what you're referring to by the "stupid ...
    (Focus-Microsoft)