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.
