Home / server / Questions / 291209
In Process
KallDrexx
Asked: 2011-07-18 07:52:50 +0800 CST

How do i grant my windows user access to a production database restored to my local machine?

  • 772

So I am trying to migrate schema changes to my production system. To do this I want to first test the schema changes on a local backup of my production database, so I downloaded the latest backup from the server, restored it to my localhost's Sql Express 2008 R2 setup, and applied all my schema changes.

Now I want to do a quick run-through of the system with the schema changes applied to do a quick verification. However, my dev environment is setup to login to Sql Express using my local windows login, and while I can run queries against the database backup when I attempt to log into the database with my Asp.NET application I get a Login Failed For User 'Matttop\KallDrexx'

My connection string is <add name="MyJobLeadsDbContext" connectionString="Data Source=localhost\sqlexpress;Initial Catalog=myjobleads_prod;Integrated Security=SSPI;" providerName="System.Data.SqlClient" /> and the only change I made to use the backup database is to change the db name in the connection string.

Why is my Asp.Net application unable to access the backed up database and how can I grant my local user access?

sql-server-2008 security asp.net

1 Answers

  1. Looks like you need sp_change_users_login 'update_one', '<db_user>', '<server_login'.

    Even though the same login name might exist on your local instance as on the server, the local login may have a different sid to that from the server. This procedure will relink the database user to the server login.

    • 0