Solved

Select permission denied on object

Posted on 2009-05-05
11
597 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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