Solved

Select permission denied on object

Posted on 2009-05-05
11
596 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
  • 5
  • 5
11 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:kmoore9611
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:kmoore9611
Comment Utility
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
Comment Utility
don't get you 100%.... do you want to know which user has rights for which database?
0
 

Author Comment

by:kmoore9611
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
glad to know that your issue is solved.

Ritesh Shah

http://www.SQLHub.com
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now