SQL Server – Mapping Multiple Server Logins to One Database User

permissionssql servert-sql

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).

Is it possible to log multiple server logins (sql server authentication) to a single database user (which has permissions assigned by being member of a database role)?

I have dozens of sql logins (sql server authentication) which need to read one setting from a central database and I'd rather map all these logins to a single DB user in the target database than create an own DB user for each login.

If yes, what would be the correct T-SQL syntax?

Best Answer

First to make sure of terminology. A Login is an instance level security principal (sys.server_principals) and a User is a database level security principal (sys.database_principals). They are joined together by an SID (security identifier). If you look in the system views above you can see how they are joined together in a 1:1 format by SID. That's 1 Login to 1 User in a database. A Login can have multiple Users but they must be in different databases.

So you will need to create a User in your database for each Login that you want to have access to it. It's a simple enough command.

CREATE USER [UserName] FROM LOGIN [LoginName]

From there you can put all of your users into a single (or multiple) Role. A role is a container that has permissions and shares those permissions with each User (for database roles) or Login (for instance roles). There is a built in database role called db_datareader that gives read access to every table and view in the database. You could add all of your users to that role. However, a better idea would be creating a new role and adding it to the db_datareader role. Then add all of your users to the new role. The benefit here is that if you want to add additional permissions to the group you can by simply changing the permissions on the role.

Create the role by:

CREATE ROLE RoleName

Add a user to a role (or one role to another)

EXEC sp_addrolemember 'RoleName','UserName'

or if you are in 2012 or higher

ALTER ROLE [RoleName] ADD MEMBER [UserName]

Related Solutions

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

Sql-server – User mapping problem during deployment

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.

Best Answer

Related Solutions

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

Sql-server – User mapping problem during deployment

Related Question