473,898 Members | 2,507 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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******@midsou th.rr.com
Jul 20 '05 #1
1 3074

"Brad H McCollum" <bm******@midso uth.rr.com> wrote in message
news:52******** *************** ***@posting.goo gle.com...
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******@midsou th.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
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
7982
by: TJ Olaes | last post by:
Hello all, this is my second post to this newsgroup. It's a question about stored procedures and permissions and how these behave between databases. Here's the scenario. I have a database that stores information for a system "A", and I have a different database on the same SQL server that stores the login and other info "LOGIN". I write a stored procedure in the "A" database that checks some tables in the "LOGIN" database, let's call...
13
3051
by: Jeager | last post by:
Why is it, Microsoft manage to write operating systems and office applications with every bell and whistle facility known to man. Yet, even after years and years of development they still cannot produce a decent version of MS SQL Server, one without a plethora of flaws and limitations? Personally I'd sack the MS SQL Server Chief Architect, start addressing some fundementals and do what MS do best - copy the best functionality of their...
2
20018
by: aaj | last post by:
Hi all I have a stored procedure that has the line EXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E' If I run the SP from an access front end as a trusted user or from a scheduled job it runs fine and exectues the dts. If I run the stored procedure using VB6 as a standard connection the dtsjob
0
1518
by: K Finegan | last post by:
I have an archival process on a large database that runs once a month. At the beginning of the process the triggers and indexes on the tables whose data is moved are dropped, the data is moved and then the triggers and indexes are recreated at the end. This produces a massive improvement in performance. The problem is the process is supposed to run on users accounts (thats the way the front-end is set up) and they don't have the...
2
3637
by: teogra | last post by:
I have written an stored proc that reads from a text file and executes the script as dynamic sql. If the text file contains malicious code,I want to be able to detect it and prevent the stored procedure from executing. I've tried revoking delete,insert,update rights all tables in the database to the user . I then granted execute rights to the stored procedure for the same user. But the user is still able to delete a record from the...
16
2222
by: Lyle Fairfield | last post by:
There is an MS-SQL table named Bugs_Comments_and_Suggestions. There is a form named Bugs_Comments_and_Suggestions. To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions to the db and SELECT permissions on the stored procedure which is the record source for the BOUND form. To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we GRANT John Doe "UPDATE, INSERT, DELETE" permissions on the table,
2
1739
by: Mike Hutton | last post by:
I have a rather odd problem. I have a SP which uses temp. tables along the way, and then returns a table of results: CREATE PROCEDURE dbo.usp_myproc( @pNameList VARCHAR(6000) ) AS
8
2815
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC
2
1353
by: Ilyas | last post by:
Hi all I need to implmenet paging across different tables. The tables all have a different name eg Data01, data02 data03 etc, however they are columns which are common to each table, but each table also has some unique columns My questions is that I want to display data from any one of these tables - I wont know which one until runtime, but since they contains large amounts of data, I only want to display say 10 at a time. Also
0
9993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10854
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10480
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9658
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8034
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5876
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4701
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3303
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.