After migrating from SQL Server 2000 to SQL Server 2008 R2, we have a few domain accounts that appear to the new server as SQL Server accounts. These accounts can SELECT from tables in the database they are assigned to, but they are not able to write to them as they could under SQL Server 2000. The SQL Server 2008 R2 log shows that at least one account failed at its attempt to INSERT into a table with this message (excerpt):

Attempting to use NT account name with SQL Server Authentication....

This specific account had no problems doing its thing on SQL Server 2000, and nothing has changed at the account level, but the database it is assigned to "thinks" this account is a SQL Server account. Has anybody experienced this issue and resolved it?

  • Note, we had to install SQL Server native drivers for the applications so they could get to the SQL Servers via ODBC (legacy apps). – Bill Dickinson Jun 15 '12 at 16:54
  • how did you migrate from 2000 to 2008, was it a new server or did you update the same server, because usually migrating to different server doesn't migrate the users and their privileges, you need to migrate users in a different way – AmmarR Jun 15 '12 at 17:28
  • How are they connecting to the server? – Aaron Bertrand Jul 19 '12 at 14:22

The question is rather vague, or at least to me it is.

You state you had new user accounts 'appear' when you migrated, but then state that 'they can only SELECT and can't write to the database like they used to in 2000'. Did these same user accounts exist in the 2000 instance or did they appear only after creating 2008? Were they created by someone or did they literally just appear, like the service users typically do (such as DOMAINNAME\NTAUTHORITY).

Also when you migrated over, did you create new domain controllers? If so, your user SIDs no longer match. Did you rename your domain? If so, your sql server users are still referenced by the old domain which means you want to script their permissions out by right clicking on them, script user as, create. Then change the user name to reference the new one. Make sure you then include this user in all the databases it needs access to.

If you did in fact create a new domain, make sure you update your internal SQL Server name to reference the new Fully Qualified Host Name: http://blogs.msdn.com/b/cindygross/archive/2010/02/11/how-to-rename-sql-server.aspx

There's a lot of unknowns, please further clarify. Thanks!


resolve permission issues when you move a database between servers that are running SQL Server

  • Would you be willing to flesh this out with a summary of what the link says? – Jack Douglas Oct 17 '12 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.