November 5, 2008 at 5:35 pm
I have a database that I had to restore to a new SQL server. I can only access the database through the web application when I use the sa user.
The previous user is not there but when I create it I still can't access it... I spoke to the owners of the application and they asked me to create it the user and give it owner rights.
WHAT i DID:
* I created a user under the server security
* I went to the properties of that user and set the default database to the DB1 (the database i'm dealing with)
I'm missing something, a step... can you please direct? How to create a user and give him database owner rights to a database? or how to verify permissions are correct on this database? thank you!
November 5, 2008 at 6:16 pm
when you restore a database on a DIFFERENT server, you get something called "orphaned users" ; remember a database with a login "Bob" on my server is not the same guy as "Bob" on YOUR server that's your issue in a nutshell.
The database you restored might have a user named "webuser", but the new server's master.syslogins may not have a user named "webuser", or even if it did, it has a unique sid that is different than the database you restored referenced...
here's an MS link describing the issue:
http://msdn.microsoft.com/en-us/library/ms175475.aspx
it pretty much boils dow to discovery and fixing the login with this command:
sp_change_users_login @Action='Report';
sp_change_users_login @Action='update_one', @UserNamePattern='yourwebuser',
@LoginName=''yourwebuser'';
after that is squared away(if that is your issue), you can the the specific database in question and expand it, then go to the folder "Security", the drill down to "Users" right click to get "Properties" on your user, or double click.
in the bottom list of "Roles" check the checkbox for "Db_owner" to give him full rights.
Lowell
November 6, 2008 at 9:14 am
Oscar,
If you have several user accounts that are used to access the application, you can always create a script using an MS stored procedure to transfer these logins over to the new SQL Server so that the login name, password and rights to the database are transferred. Below are links to the MS KB articles that explain how to do this.
- SQL Server 2000 - http://support.microsoft.com/kb/246133
- SQL Server 2005 - http://support.microsoft.com/kb/918992/
Just read through these articles to ensure you are following \ using the correct procedures (ie: transferring logins from SQL 2000 to SQL 2005, etc). If you follow this correctly, it will transfer the logins and passwords to the new instance of SQL Server which is extremely helpful.
Lastly, when you actually execute the stored procedure and the output generated contains the scripts to recreate all the logins, you can go through that script and delete any of the logins that you don't want to transfer (ie: logins for applications that are being migrated to the new database server).
This is just another way to do it as the post above will help you with fixing the orphaned user and granting the db_owner privelages.
Hope this helps some.
Brian
November 6, 2008 at 12:50 pm
Ok this is what the application support told me
"Yes, you are correct, just recreate the user and give it proper permissions, making sure that the user is the Database Owner."
When I run
sp_change_users_login @Action='Report';
it comes back empty.
When I run:
sp_change_users_login @Action='update_one', @UserNamePattern='yourwebuser',
@LoginName=''yourwebuser'';
It gives me this:
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'helpspot_user' is absent or invalid.
The user IS created though.
November 6, 2008 at 12:50 pm
Brian I will look into your post and get back to you now...
November 6, 2008 at 1:04 pm
Yeah Brian..... I don't know if that would work... that is to move from one server to another... 2000 to 2005...
I just had to reinstall the original server so I don't have the original SID.
November 6, 2008 at 1:08 pm
Ok - sorry, I thought you were just moving the database to another new SQL Server.
Since you had to reinstall it, the other way that I've resolved those issues is to follow the steps above to fix the orphaned account. If you created the SQL Login(s) with the same user names, it should fix the orphaned accounts and then it should start working again.
November 6, 2008 at 1:28 pm
Yeah I tried but no luck :unsure: see my post above... that's what I get.
I don't know what else to do
November 6, 2008 at 1:32 pm
Give this a shot:
- EXEC sp_change_users_login 'Auto_Fix', 'user'
All you need to do is just change the 'user' variable to whatever the SQL Server Login is. For example, if I created my new SQL Server Login called 'Oscar' and within the database itself, under the Users folder, I see a user named 'Oscar' there, the command would like the one below:
- EXEC sp_change_users_login 'Auto_Fix', 'Oscar'
Let me know if that helps.
November 6, 2008 at 1:41 pm
ok.... I'll give it a shot BUT I have a question. When I restore the database there are NO user inside. So what do I do?
Create a login under the server called "webuser" and then create a user under the database called "webuser"?
November 6, 2008 at 1:46 pm
Oscar,
If there are no users listed there when you restore the database, then this is what you will need to do:
1. Create a new SQL Server Login, set the password, default database, etc.
2a. If you are using SQL Server 2005, click the User Mapping page, check the box next to the database this login needs access to and then in the Database role membership window, check the box next to db_owner.
2b. If you are using SQL Server 2000, click the Database Access tab, check the box next to the database this login needs access to and then in the Database roles window, check the box next to db_owner.
3. Now test the connection using your application.
November 6, 2008 at 1:54 pm
Brian perfecT!!!! working like a charm!!
I was over doing it in the settings before... I knew it had to be simpler or that I was missing a checkmark or something...
Thanks again!! I can go home now that the program is working hahahaha!! 😀
November 6, 2008 at 1:56 pm
Glad to hear it's working!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply