Select Page

Art of BI: Transferring SQL Server Logins Across Server Instances

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:

[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]

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS