Wednesday, January 02, 2013 8:04 PM
I have found:
The other way that this issue can be reproduced is by dropping the windows user and adding it back again. The reason this issue occurs is because SQL Server maps logins using SIDs. When the SID changes at the Windows/Domain level, the SID stored in the SQL system catalog is not updated. When an attempt is made to use that login to access SQL, a SID mis-match occurs which results in the error.
but I would rather not drop and add 500 users to specific databases
Wednesday, January 02, 2013 10:37 PM
Somehow these users need to have access to the server on server level. However, you should not have to each 500 of them one by one, but if there is a suitable Windows group, you can add them all in one bang.
If you check the old server, you can probably find out which group that is.
By the way, there is no SQL 2003. There was SQL 2000 and SQL 2005.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
Thursday, January 03, 2013 12:12 AM
As per erland you can do by adding them to a windows group and add back this group to your SQL server. THere is work around in case if you are not comfortable that way.
Script out all your users with permissions on all objects from the original database. After migrate drop them all in one go and run your script in one go. you will get all your users recreated.
otherwise run the script to find out the orphned users list and fix the SID. google for revlogin. thet will help you to update the SID.
Thursday, January 03, 2013 11:38 AM
You aren't using most correct way to move databases. As rightly mentioned above there is nothing sql 2003, please verify that first to start with.
How to transfer logins and passwords between instances of SQL Server - Steps to transfer logins.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.
Thursday, January 03, 2013 1:24 PM
Neither one of those links address Error: 18456 Severity: 14 State: 11. but they do address other states. The first link is not what I am looking for and the second link is for a different Error state as well, but thank you for trying.
- Edited by skdoc36 Thursday, January 03, 2013 1:25 PM
Thursday, January 03, 2013 1:27 PM
I am going to try the script on the link and let you know how it works.
Thursday, January 03, 2013 1:44 PM
I tried it, but this only give me the 5 server logins. I have deleted them all and ran the script with no luck. Also I have tried to recreate them manually with no luck
Thursday, January 03, 2013 2:00 PMCould it be a Domain issue such as
Thursday, January 03, 2013 11:07 PM
When you script out and create you may able to create as SQL users. You can only connect to SQL server using this user and pass word.
If you use windows authentication you never able to connect because They do not have permission in windows level. They are SQL server users not windows users. When users do not exist you can not get their windows SID.
You and your net work admins to recollect the knowledge of impersonation. create one user or group in windows and use to your SQL group. Below are the links to refresh...
Friday, January 04, 2013 3:22 AMModerator
After checking your post, it seems that you are confused with SQL Server logins and database users. When we login in SQL Server instance, we need a login account rather than a database user account. After moving databases from a SQL Server 2005 to SQL Server 2008, please use a valid login account on SQL Server 2008 rather than an login account on SQL Server 2005. Regarding the orphaned users after moving the databases, please refer to the following document resolve them:
Troubleshoot Orphaned Users (SQL Server)
TechNet Community Support
Friday, January 04, 2013 1:15 PMI do not have orphaned users. I have recreated the logins from the sql server 2005 on the sql server 2008 box.
Wednesday, January 09, 2013 3:11 AMModerator
Hi, do you mean that you have migrated the original logins with SQL Server 2005 to SQL Server 2008? Since you are not sysadmin, how did you achieve this? Additionally, in your first post, you said:
>>if the user is not in the Servername->security->logins, they get this error.
Yes, this is normal, SQL Server requires valid logins (in Servername->security->logins) when logging in. If you tried to login SQL Server with a database user, it will fail. You can refer to the following document about the difference between a SQL Server login and a database user.
TechNet Community Support
- Marked As Answer by Allen Li - MSFTModerator Wednesday, January 30, 2013 3:28 AM