[AccessD] SQL Server as a BE

I have a simple routing that I use in my forms, as such:

Here's a menu forum:

Private Sub Form_Load()
        Me.cmdHelpDesk.Enabled = IsMemberOf("HelpDesk")
        Me.cmdRcv.Enabled = IsMemberOf("Receiving")
        Me.cmdService.Enabled = IsMemberOf("SvcTech")
        Me.cmdAccounting.Enabled = IsMemberOf("Accounting")
        DoCmd.Restore
End Sub

Here's my function:
Public Function IsMemberOf(Role As String) As Boolean
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call the stored procedure, passing it the parameter, returning recordset rs
CurrentProject.Connection.stpCheckRole Role, rs
'Fill in the fields from the returned recordset
If Not rs.BOF And Not rs.EOF Then
    If Nz(rs![Allowed], 0) = 1 Then
        IsMemberOf = True
    Else
        IsMemberOf = False
    End If
Else
    IsMemberOf = False
End If
rs.Close
Set rs = Nothing
End Function


Here is my sproc:

CREATE PROCEDURE stpCheckRole(@Role  AS VARCHAR(50))AS

--debug variable declaration:
--DECLARE @Role  AS VARCHAR(50) SET @Role = 'SvcTechMgr'

if (is_member(@Role) = 1) OR (is_member('db_owner') =1)
SELECT 1 AS Allowed
ELSE SELECT 0 AS Allowed


HTH
David McAfee

On Tue, May 19, 2009 at 5:14 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> One of my old clients had a company migrate their database to SQL Server.
>  They were told they
> needed to do this for HIPAA compliance, so that they could easily encrypt
> personal information.
>
> Well... they told my client that they would never even know that it had
> happened and of course...
> things are broken, which the company is now trying to blame on Access.
>  Bottom line, they are now
> being fired and I am coming back to pick up the pieces.
>
> I have only used SQL Server with small databases (which this is) and in
> those cases I used Windows
> Authentication.  If they used SQL Server group / user can you give me a
> very basic rundown on how
> this would work (general terms).  I understand the user / group metaphor, I
> have just never applied
> it to SQL Server.
>
> Inside of my existing SQL Server databases I use Windows authentication and
> my objects all require
> dbo to access.  What happens if groups are used?
>
> I do love the "learn under fire" scenario.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>