Sql-server – User mapping problem during deployment

sql serversql-server-2008-r2

I have a development machine with a new database I have designed. The server has a login L1 and the database has a user U1. L1 is mapped to M1 and tables have permissions set up for U1. Authentication is SQL Server authentication. I wanted to fully backup the database and restore to the production server so that I could start to use it in production.

I had a problem with user U1: I could not map it to any login on the production server. So I could not use U1 with any login. Is this correct behavior?

Best Answer

You have to create a login on the prod server as when you restore the backup to PROD server, you are having an orphaned user.

So steps are :

Create Login using SID option (if you are just having 1 login to map). Else use sp_help_revlogin
You can use SID from sys.database_principals where type_desc = SQL_USER (since you mentioned you are using SQL Login).

Then run ALTER USER as below :

USE [DatabaseName]
go
ALTER USER [UserName]
WITH LOGIN = [UserName]

Note: sp_change_users_login is deprecated.

Related Solutions

Sql-server – Database Users and Server Logins and the Mapping between them

Logins or server logins exists at the SQL server level, and the database user is at the database level. They need not have the same name, but generally do. In an instance, you cannot have a database user function properly without a corresponding SQL server login.

Let us say you have an instance: instanceA with a sever login userA on your computer. You can map this server login userA to a database user: userA for a database DB_A. They are both named userA, but one is the server login and one is the database user. The database user: userA resides in DB_A. When you backup and restore DB_A from instanceA to another instanceB, the database user: userA is moved, but the server login: userA still remains on instanceA. To move the logins from instanceA to instanceB, you can use sp_help_revlogin (Click to check out KB page).

If you had previously created the SQL server login userA on instanceB, then SQL will try to map this login with database user: userA, when DB_A is restored from instanceA to instanceB. If this fails, you can use the sp_change_users_login (Click for BOL link) procedure to fix the broken mappings. Hope this clears things up.

Sql-server – User in multiple windows groups mapped to sql logins

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login. ~~And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2.~~ And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2

Best Answer

Related Solutions

Sql-server – Database Users and Server Logins and the Mapping between them

Sql-server – User in multiple windows groups mapped to sql logins

Related Question