SQL Login Error (1 Viewer)

KACJR

Registered User
Joined
Jul 26, 2012
Messages
57
Greetings to the well of knowledge...


I have an Access front-end that talks to an SQL Server 2008R2 back-end (yes, I know I need to upgrade this). This front-end is used by users on a Server 2008R2 remote desktop server.


I am in the process of completing the build-out of a new Server 2012R2 remote desktop server. To make the migration easier, I have created new users for this server and have created SQL Logins for these users using the same properties as their old SQL logins.


When I launch the front-end in a session on the new RDP server using one of the new logins, I get SQL Error 18456. I checked the properties of the old and new users and they appear to be identical. I can't figure out why I'm getting the SQL Login error.


Any thoughts? I'm baffled.


Thanks,
Ken
 

Minty

AWF VIP
Joined
Jul 26, 2013
Messages
6,529
Check that the users/groups they are in have permissions on the database?

That error although credential-based can still be a result of permissions.
 

SQL_Hell

SQL Server DBA
Joined
Dec 4, 2003
Messages
1,338
Did you create the users first and then the logins? It should be the other way round, you create a login and then create a user that is linked to that login.

It is possible that you have orphaned users.

Run the following query in sql server management studio against your database to find out whether you have any orphaned users.

select p.name,p.sid
from sys.database_principals p
where p.type in ('G','S','U')
and p.sid not in (select sid from sys.server_principals)
and p.name not in (
'dbo',
'guest',
'INFORMATION_SCHEMA',
'sys',
'MS_DataCollectorInternalUser'
) ;
 

KACJR

Registered User
Joined
Jul 26, 2012
Messages
57
Did you create the users first and then the logins? It should be the other way round, you create a login and then create a user that is linked to that login.

It is possible that you have orphaned users.

Run the following query in sql server management studio against your database to find out whether you have any orphaned users.

select p.name,p.sid
from sys.database_principals p
where p.type in ('G','S','U')
and p.sid not in (select sid from sys.server_principals)
and p.name not in (
'dbo',
'guest',
'INFORMATION_SCHEMA',
'sys',
'MS_DataCollectorInternalUser'
) ;

BINGO! Thank you so much for that insight!
 

SQL_Hell

SQL Server DBA
Joined
Dec 4, 2003
Messages
1,338

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom