Check if logged in user has RW permission to a table

[JordanCN2]

I am using MS Access as a front end to a SQL 2000 (Soon to be 2008) back end. Is there a simple way for me to check if the current logged in user has read/write access to a table.

I secure the back end tables with Windows Integrated security. Currently we are small so users do not usually try to enter data into forms that are not their job, but now that we are expanding and people are trying to pass off some of their work to people who should not be doing it I would like to avoid the users getting the SQL Error at the end when they go to save the record.

I know I should think about developing the security table showing the forms users have access to, but I need some time to map out a plan for that. I am hoping I can just check if the users has Read or RW access.

 

[SQLBill]

Here is a start for what you want to do:

SELECT dp1.name, dp2.name
FROM sys.database_principals dp1
  JOIN sys.database_role_members drm
    ON dp1.principal_id = drm.member_principal_id
  JOIN sys.database_principals dp2
    ON drm.role_principal_id = db2.principal_id

All you need to do is add a where clause to limit what is returned to what you actually need.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mrdenny]

thread962-1324602 has code for SQL 2000 and higher on how to figure out what permissions a user has.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog