newbee question on finding a users privilages on a sql db

[Jake Peters]

I'd like to know how to get a users various table
privilages from a sql server (insert/delete/update), so
that i can set controls to behave accordingly (read only,
hidden, disable "insert" button, etc). I'm sure it can't
be too difficult, but I've been having a hard time finding
any revelant information when doing searches.

I know sql can just throw back an exception to let the user
know, but my goal is to make custom controls that get these
properties from a custom parent control that contains the
security info, as well as other properties (max length for
columns, etc).

Any help would be much apreciated.

 

[One Handed Man \( OHM - Terry Burns \)]

why not add a UserRoles table and query this instead on form load, to check
the role the user is in and then set the UI accordingly

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

 

[Mary Chipman]

All well-designed SQL Server applications use role-based security, so
you'll need to examine role memberships, not just explicit
permissions. User, role and permission information is stored on the
server, and is documented in SQL BooksOnline. You can download the
most recent build at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.

--Mary

 

[Jake Peters]

Thanks for the advice thus far. I really like the
flexibility of roles... However, being relatively new to
database programming, some examples would help greatly.
I've found examples on looking up which role a user is in,
but nothing that tells me what access the role has for a
given table/column. The documentation on roles has alot on
how to set permissions, but nothing (that i could find
anyway), to look them up or create a table/dataset with the
information stored in it.

Any pointers would be great.

And as a side note, if you can recommend any books on the
topic of creating .NET applications for sql server then by
all means, recommend away.

 

[Mary Chipman]

Checking permissions on database objects at runtime is not something
you really want to do any more than you want to trigger an exception
because both operations are fairly expensive, requiring round-trips to
the server. One way to handle it would be to hard-code role
permissions in the FE, then when the app starts, find out what role
the users is in, and don't let them load any objects that the role
doesn't have permissions on. That saves on server round-trips. This
is even further simplified if you use stored procedures for everything
because you only have one set of objects to check and one permission,
execute. Narrowing database access to parameterized stored procedures
is a good strategy security-wise to ward off SQL injection attacks and
strictly limit the range of activities users can have in the database.
I'd recommend browsing amazon.com for books because they have reviews
and sample chapters you can take a look at. I like David Sceppa's
ADO.NET for getting started. You also need a good book on SQL Server
-- Ken Henderson's are good, and there are many other excellent
choices as well.

--Mary