Solved

Select permission denied on object

Posted on 2009-05-05
11
599 Views
Last Modified: 2012-05-06
I'm having a problem with a database migration. A client is moving the application database from Server 2000 to Server 2005. After restoring the db in 2005 they tried to setup the logins/users but they keep getting the error "The SELECT permission was denied on the object 'Company', database 'CDMS', schema 'dbo'." when starting the application. Giving the login admin rights as a server role fixes the problem. The database users were all added with the restore of the database so I had the IT guy try deleting a user from both the database and the server login and recreate it from scratch. After adding the login, checked off the databases that the user was allowed access to. Then had him add a user in the database for that login and grant db_datareader and db_datawriter roles. This is how it was setup in 2000 and trying it on our development system with server 2005 this works properly.

What am I missing? It sounds like the user at the database level is not mapped properly to the login but from what I'm being told it was done correctly.

Thanks

Ken
0
Comment
Question by:kmoore9611
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 250 total points
ID: 24309881
backup and restore of database do not transfer the logins to the new server. You should create the scripts of logins from the old server and recreate them by using that scripts in the new server. here is an article about the subject

http://support.microsoft.com/kb/246133
0
 

Author Comment

by:kmoore9611
ID: 24311126
This link is very useful, thank you. However, the problem is happening for new logins added after the database has been restored on the new server.
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 total points
ID: 24311557
exactly the same you want, I faced the same problem, overcome it and shared it with people in my blog.

http://www.sqlhub.com/2009/04/fix-user-and-login-synchronization.html
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:kmoore9611
ID: 24318233
Also a good script for upgrading but does not seem to correct the problem. It's not just existing users from the imported database that are not working it's also any new user that are added. These are the steps I had the cleint try:
Create a new windows user account
Add that user as a login on SQL Server. Set default database as the application database. Left user mapping alone.
Add user to the database using that login. Set default schema as dbo. Add db_datareader and db_datawriter roles.

Trying to logon to the application with that user results in the Select Permission denied error. Giving the login sysadmin server role fixes it.

This setup works on our development system on an imported db but not at the client site. They are a fair distance away so going on-site is not very practical. I'm clearly missing something but I have no idea what.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24321994
well, can you please tell me the user you are using having which fixed server role?

have a look at my small script at

http://www.sqlhub.com/2009/05/find-database-principal-and-its-member.html

it will tell you which user has which fixed server role.
0
 

Author Comment

by:kmoore9611
ID: 24344340
I will send this script to the client Monday to run on their server. They have a couple hundred users in the database but I will focus on one for testing.
Is there a way to show which server login is connected to which database user?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344817
don't get you 100%.... do you want to know which user has rights for which database?
0
 

Author Comment

by:kmoore9611
ID: 24345268
That's pretty much what I'm looking for. What I want to see is if the link between the server login and the database user is good. It seems like the login isn't picking up the roles that are set for the database even though it looks like the users are setup properly in the database. I hope that's more clear. I'm not very familiar with SQL Server 2005 and I'm not a DBA but the client doesn't have a DBA either so it's up to me to find an answer!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24346889
well there is a function which will give you all available permission on one database

select * From fn_my_permissions(NULL, database)

now, if you want to see how many user has permission on your database, you can probably run query provided above in CURSOR and get the list.
0
 

Author Closing Comment

by:kmoore9611
ID: 31578242
Sorry it took so long to get back to this. The client in having this problem dropped the issue for a long time. I'm now told the problem has been resolved.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24969264
glad to know that your issue is solved.

Ritesh Shah

http://www.SQLHub.com
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question