[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select permission denied on object

Posted on 2009-05-05
11
Medium Priority
?
605 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 750 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 750 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

649 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