Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Today I migrated a database from SQL Server 2005 to SQL Server 2008 R2 using the backup/restore method which went fine and did not generate any errors.

I can log into SQL Server 2008 through the SQL Server Management Studio using NT Authentication under the Administrator user and view the tables, run select statements, etc, the Administrator user is the owner of the database that I migrated.

The problem is, when I log into SQL Server using NT Authentication under a different username I cannot run select statements or view the tables, views, stored procedures or anything.

For example, whenever I run a select statement I get the error

The SELECT permission was denied on the object 'AAPUsers', database 'PINT', schema 'dbo'.

When I try to expand the tables tree I get the error

The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I have tried giving my other user full permission to everything I can find but the problem continues.

I have found that if I create an SQL Server user and login in with that user and give them db_datareader and db_datawriter access to the database then it all works fine, its just when I use NT Authentication.

I have tried giving the domain user (eg domain/user) the above permissions but it didn’t fix the problem. If I make my domain user the owner of the database then everything works, as one would expect.

I also created a test database on SQL Server 2005 with 1 table and migrated that to 2008 using the same backup/restore method and once I have given my domain user db_datareader and db_datawriter access it works fine so its something to do with that particular database but I can figure it out.

The problem seems to be around NT Authenticated users, I'm sure it just something simple that I need to do.

I've tried everything I can think of and am out of options.

share|improve this question

migrated from stackoverflow.com Jun 24 '11 at 17:01

This question came from our site for professional and enthusiast programmers.

1 Answer 1

Verify that there isn't something wrong with the mappings between Logins(server level)/Users (database level). One common error with migrations like this is that the admin moves the database but foregets that the logins that the users are mapped to do not exist on the new instance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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