[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

Select permission denied on object

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
kmoore9611
Asked:
kmoore9611
  • 5
  • 5
2 Solutions
 
tigin44Commented:
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
 
kmoore9611Author Commented:
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
 
RiteshShahCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
kmoore9611Author Commented:
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
 
RiteshShahCommented:
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
 
kmoore9611Author Commented:
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
 
RiteshShahCommented:
don't get you 100%.... do you want to know which user has rights for which database?
0
 
kmoore9611Author Commented:
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
 
RiteshShahCommented:
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
 
kmoore9611Author Commented:
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
 
RiteshShahCommented:
glad to know that your issue is solved.

Ritesh Shah

http://www.SQLHub.com
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now