How to write a function that will return true if user is in a certain role?

[Tom Dauria]

I have a SQL database with an Access front end. In the database I
have a read only and a read write role. When a read only user opens
the database I want all the fields on the form to be locked so that
the user will not try to change data and get an error from the server.

Right now I am doing that with a table. But it's a hassle to have to
maintain a table when if I could answer the question is the currently
logged in user in the read write role?

My server is running SQL Server 2000. So I was wondering if I could
write a function to do this? The function would take the role being
checked as a text parameter and return true if the currently logged in
user is in that role or false if he/she isn't.

[Erland Sommarskog]

Tom Dauria (td*****@bu.edu) writes:

I have a SQL database with an Access front end. In the database I
have a read only and a read write role. When a read only user opens
the database I want all the fields on the form to be locked so that
the user will not try to change data and get an error from the server.

Right now I am doing that with a table. But it's a hassle to have to
maintain a table when if I could answer the question is the currently
logged in user in the read write role?

My server is running SQL Server 2000. So I was wondering if I could
write a function to do this? The function would take the role being
checked as a text parameter and return true if the currently logged in
user is in that role or false if he/she isn't.

Yes, you could probably write a function.

But it is not clear to me whether you are using SQL Server roles or
roles you have defined on your own.

If you are using SQL Server roles, you can simply say

SELECT is_memeber('readwrite-role')

So you don't even need a funcion in that case.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp