SQL server user login mystery revealed

well today i wen to deploy our project build at the client,

one strange thing happend

we have the follwoing scenario.

as usual  a db server and we have a database say client_db, for this db we created a user client_user in our dev environment , all our tables were previously associated with dbo user ,but then we update them and associated with cleint_user , so fqn has cleint_user.tablename etc. to deploy our applicaiton we took the backup of our development db and restored it on deployment db ,(dropped the earlier db at deployment server),and guesswhat

we run the app , and login failed mesg starts poping up 😦 too bad 😦
althoug the client_user login ws tehre on deployment server

after several attempts we were able to resolve this by using

sp_change_users_login ‘Auto_Fix’ ‘cleint_user’

the issue is related to the linking of users and logins

chek these lnks , they will explain the sitution more clearly , this is called Orphan User 🙂

http://vyaskn.tripod.com/troubleshooting_orphan_users.htm

http://www.akadia.com/services/sqlsrv_logins_and_users.html

one my senior hb told me another simple soultuiin

on deployment system,
drop the db
login wtih cleint_user
create cleint_db
resoter cleint_db from backup

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s