SQL Login Error (1 Viewer)

KACJR

Registered User.
Local time
Today, 17:08
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
Local time
Today, 22:08
Joined
Jul 26, 2013
Messages
6,622
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
Local time
Today, 22:08
Joined
Dec 4, 2003
Messages
1,340
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.
Local time
Today, 17:08
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
Local time
Today, 22:08
Joined
Dec 4, 2003
Messages
1,340
OK good stuff, if you need any help with fixing orphaned users then please let me know.

As a side note, I always use the following procedure for migrating logins, it generates a script of the login, which keeps the SID (login primary key) the same. You can use the script to create logins in all your environments and you will never see any orphaned users ever again.

https://support.microsoft.com/en-gb...and-passwords-between-instances-of-sql-server
 

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

Top Bottom