Share via

SQL Server Error: Msg 229, Level 14, State 5 in SQl Server 2008

  • Article

Question

Wednesday, June 24, 2009 12:52 AM

Hello All,

I'm migrating my database structure from SQL 2000 to SQL 2008 Server. I backed up my database and restored in the new server.

Here is the issue I'm having.

Whenever I'm trying to access tables using my client applications, I'm getting the following error:

**Server: Msg 229, Level 14, State 5
The SELECT permission was denied on the object '<tableName>', database '<databaseName>', schema 'dbo'.
**
NOTES:

  • I'm connecting to the server using SQL Server Authentication mode.
  • I've given, my login user, the access permission up to the table level.

Your thoughtful solution is greatly awaited and appreciated.
Thanks.

All replies (2)

Wednesday, June 24, 2009 1:14 AM | 2 votes

 

You likely have a mismatch of SQL logins and users due to the restore from another server.  This can be fixed with sp_change_users_login:

 

EXEC sp_change_users_login 'Update_One', 'my_login', 'my_user';

See the Books Online for more info.

--
Hope this helps.

 

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang /

"Sanjay_Gupta_Atl" wrote in message news:46754135-6f5a-4fa b-9d07-2f5640dc2bbd...

Hello All,

I'm migrating my database structure from SQL 2000 to SQL 2008 Server. I backed up my database and restored in the new server.

Here is the issue I'm having.

Whenever I'm trying to access tables using my client applications, I'm getting the following error:

**Server: Msg 229, Level 14, State 5
The SELECT permission was denied on the object '<tableName>', database '<databaseName>', schema 'dbo'.
**
NOTES:

  • I'm connecting to the server using SQL Server Authentication mode.
  • I've given, my login user, the access permission up to the table level.

Your thoughtful solution is greatly awaited and appreciated.
Thanks.

Wednesday, June 24, 2009 7:32 AM

you should use ALTER USER command to remap an orphaned user to
an existing login.

ALTER USER user_name WITH LOGIN = login_name

István Sáfár