April 15, 2012 at 11:55 pm
jim.hamer (4/15/2012)
Perry, I ranexec sp_change_users_login 'report'
but it returned nothing.
Are you sure you set the correct database focus for the query window, you didn't run it in mster did you?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 4:46 am
irrespective of the connection string is there a 'use database' statement in the application.
would be worth running profiler to see exactly what happens when you fire up the application
---------------------------------------------------------------------
April 16, 2012 at 6:26 am
Yes, I ran exec sp_change_users_login 'report' with the focus on the new database. It didn't return anything.
Profiler didn't give me any new information. The SPID was correct.
Let's remove the application from this problem. In SSMS I ran the following:
exec NEWdb.schema.usr_basic_sel_by_user_id @p_user_id='jhamer'
select * from NEWdb..usr_basic where user_id='jhamer'
The first statement ran the stored procedure and returned a row from the original database, which is wrong.
The second statement ran a query and returned a row from the NEWdb.
Is it possible that the user that is the schema owner still points to the original database? I could create a new user and reassign the ownership of the schema but I'll lose the rights to the stored procedures when I do this. The whole point of copying the database was to save the effort required to rebuild the rights.
April 16, 2012 at 6:57 am
Perry Whittle (4/15/2012)
You said you restored the database back to the same server as the original and didn't have to re create any logins???
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 7:11 am
Yes, I didn't create any logins. Neither server logins nor database users. I was following the example where I restored a production database and apart from having to create a local user (ie. local to my test server), I didn't have to do anything else.
Are you suggesting that after copying the database to a new name, I'd have to create a new set of users? It will be unfortunate if this is the only solution because I'll have to recreate all my stored procedures and grant them permissions to the new users. Surely, I can avoid this.
April 16, 2012 at 8:19 am
In the properties of the database i think you should change the owner of database to "sa" in the files Do It Dear 🙂
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing
April 16, 2012 at 8:30 am
Please post the code for NEWdb.schema.usr_basic_sel_by_user_id.
April 16, 2012 at 8:34 am
Solved. Logins, users, schema and database owners are all okay.
The issue that I'm having is that I have the original database hard coded in the stored procedures. I was fooled because of my experience with the copy of the production database but I had overlooked that the stored procedure scripts where changed on the production server (to reference the production database) prior to sp being created.
The best solution will be to remove the database from the stored procedure queries.
Thank you to all who helped me with this.
Jim
April 16, 2012 at 11:51 am
also, for portability of the database do not grant permissions to user/logins grant them to database roles. Then when you restore the database as a copy all you have to do is map the user to the database add the database role(s) to their login and all permissions will be the same.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply