Art of BI: Transferring SQL Server Logins Across Server Instances

By | In Art of BI | May 02nd, 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:

[sourcecode language=’sql’]
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]

[/sourcecode]

Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

1 thought on “Art of BI: Transferring SQL Server Logins Across Server Instances”