I recently had to add a new database role for an existing Access.adp database using SQLSERVER 2008. I already have 4 other roles working just fine, but I cannot connect (We use NT Authentication) when using this new role. It bombs out and says that it's "Unable to verify login. Program Aborting". I cannot determine what I am missing. I use the following procedure to verify who's trying log into the database based on the assigned role. For testing purposes, I assign myself to the role I am testing. The schema and role have the same name. In the procedure below, schema 16384 is the SA account.
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcGetLoggedInUserData]
AS
DECLARE @user varchar(25)
SELECT @user = substring(system_user,
(charindex('\', system_user) + 1),
len(system_user)- charindex('\',
system_user))
--PRINT @user
SELECT a.name AS UserID,
UPPER(d.name) AS UserRole,
c.per_id,
c.per_code
FROM sys.sysusers a,
sys.sysmembers b,
dbo.TBL_PERSONNEL c,
sys.schemas d
WHERE a.uid = b.memberuid
AND a.name = @user
AND a.name = c.per_login
AND b.groupuid = d.schema_id
AND d.schema_id <> '16384'