By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Unable to log onto Access .adp db after creating new db role using sqlserver 2008.

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5.  
  6. ALTER PROCEDURE [dbo].[ProcGetLoggedInUserData]
  7. AS
  8. DECLARE @user varchar(25) 
  9. SELECT @user = substring(system_user, 
  10.     (charindex('\', system_user) + 1), 
  11.     len(system_user)- charindex('\', 
  12.     system_user))
  13. --PRINT @user
  14. SELECT a.name AS UserID,
  15.     UPPER(d.name) AS UserRole, 
  16.     c.per_id, 
  17.     c.per_code
  18. FROM sys.sysusers a, 
  19.     sys.sysmembers b, 
  20.     dbo.TBL_PERSONNEL c,
  21.     sys.schemas d 
  22. WHERE a.uid = b.memberuid
  23. AND a.name = @user 
  24. AND a.name = c.per_login
  25. AND b.groupuid = d.schema_id
  26. AND d.schema_id <> '16384'
Nov 23 '15 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.