Unable to verify login from Access to Sqlserver

• Equus

  SSC Veteran

  Points: 221

  More actions

  November 23, 2015 at 11:25 am

  #97470

  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. Schema 16384 is the SA account.

  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'