Art of BI: Transferring SQL Server Logins Across Server Instances
Author: Christian Screen | | May 2, 2009
A while back I was working with a client on a web application project that had a basic SQL Server database as the relational data storage system. They wanted to use a rather “locked-down” SQL Authentication mode for the credentials that would allow the web app to communicate with the database. This user had read/write ability only. However, the web application would access the database mainly through use of stored procedure calls. As we all know, if a read/write user is going to communicate via stored procedures that user must have EXECUTE permissions on each stored procedure in question. We set this up in SQL Server security and the ASP.NET web.config file of the web application and everything worked well.
We finally were requested to move their “finally” tuned developed database to their production system. So, we did. Production worked great for weeks. Then the client needed a “quick” enhancement. So, during our maintenance window we created a backup of the production database and copied it down to development to begin the work. We dropped the existing development database and restored the backup of production on the development server. We started the web application and immediately encountered problems. Eventually, we noticed that the “login” field in the user security properties was blank. This is the login name that is required by the web.config credentials and it was missing. To keep a long story short we circumvented the issue by dropping and recreating the user so the login would again be function. That was a solution that worked but it was not the right one. After more research we came across an article on SQL Server Logins and Users. Basically the problem is that the master database stores the user login information for each database user. When the database is dropped, the user still exist in the server security model and has no home to belong to. The user is basically “orphaned”.
Here is a template script to solve the issue whenever it occurs:
Use Master
GO
--// Let's see the current global system ID for this login
SELECT sid FROM
dbo.syslogins
WHERE
name = [MyLoginName]
--// User your DB in question
USE
[TheDB]
GO
--// Let's see the current global system ID for this user
SELECT sid
FROM
dbo.sysusers
WHERE name = [MyLoginName]
GO
--// Maps an existing database user to a SQL Server login. The "report" call simply lists the users and their SIDs
sp_change_users_login 'report'
GO
--// Perform the re-link
sp_change_users_login 'update_one', [MyLoginName], [MyLoginName]
--// Confirm that we are now in sync
Use Master
GO
SELECT sid FROM
dbo.syslogins
WHERE
name = [MyLoginName]
SELECT sid
FROM
dbo.sysusers
WHERE name = [MyLoginName]
Blog Author
Related Posts
How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified
The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
How to Recover a Table from an Oracle 12c RMAN Backup
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.
Data Types: The Importance of Choosing the Correct Data Type
Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.