Users lost

Question

Post reply

Users lost

Tara-1044200

SSCoach

Points: 15951
More actions
May 5, 2010 at 5:31 am

#386854

I have a sql user in a database but after backing up the database and restoring to different server i lost access to the database though the user is existing, why so ? its not the case with windows user though.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Because the sql user account needs to be created on sql server level as well, not only db level.

drop the account from the DB, recreate it on sql server level, and apply correct permissions on the specific db again.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Best practice is create/transfer login to new server and then map users(should exist in particular database)there

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lowell SSC Guru Points: 323547 More actions · Answer 3

Henrico Bekker (5/5/2010)
Because the sql user account needs to be created on sql server level as well, not only db level.
drop the account from the DB, recreate it on sql server level, and apply correct permissions on the specific db again.

Like Henrico said, the sql user "bob" on my server is not the same "bob" on your server;

behind the scenes each login has a unique SID in my master.dbo.syslogins;

the user in [yourdatabase].sys.users either does not have a matching login at all in master.dbo.syslogins, or the login has a different SID than expected. This is often called an orphaned user, adn if the login exists, you can use the procedure sp_change_users_login

http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
First, make sure that this is the problem. This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

both are same databases on Prod and Dev and has same users in it. when i insert monthly data in Dev database, i have do a backup and restore on to prod server with different name. once it is in prod i will switch over the names but i miss this sql user for which i have to drop and re create.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Tara-1044200 (5/5/2010)
i miss this sql user for which i have to drop and re create.

whenever we restore backup , users automatically get transferred (obviously orphan users) . So how did you miss it?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)