1

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?

Improve this question
3
  • 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
    Commented Jun 15, 2012 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
    Commented Jun 15, 2012 at 17:28
  • How are they connecting to the server?
    – Aaron Bertrand
    Commented Jul 19, 2012 at 14:22

2 Answers 2

Reset to default
1

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: Link

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

Improve this answer
0

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

Improve this answer
1
  • Would you be willing to flesh this out with a summary of what the link says?
    – Jack Douglas
    Commented Oct 17, 2012 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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