How to map multiple Database Users to one Login? also explain User concept practically

Question

Hello Experts,

I have basic query for Users and Login in SQL Server. I know login concept also implement practically. i.e. We can create login using windows / SQL Server authentication.

About User, Theory I understood, that User provides access permission on DB, please explain me Login and User for below -

I can do below things:-

1- Can create Login

2-Can Create user

3-But not able to map multiple users to one login

Lets say I have team of 8 people, I am admin and want to provide different access permission to other people, then How Login and Users can be implemented for this scenario.  

Thanks,

RP

Answer 1

No, you can't map several users to the same login. Create one user for each login. Create a role in the database and assign the role the permissions needed. Then add the users in the database to your role.

Or was there something different that you had in mind? 

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 2

You can create a windows group and add windows logins to and grant access  to SQL Server

You can create a SQL Login and a Database User from a Windows group

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

Thanks for reply.

Lets say my team have 100 members and want to provide different access permission to each one. Do I need create 100 Logins?

 

Answer 4

Can you define "different access permission"? If you mean that for instance one person need SELECT permissions on one table, and some other person need SELECT permissions on some other table. I.e., different privliges for the individual, then yes, you need to create the logins if you use SQL Server authentication.

But if yo use Windows authentication, then you can create only one login for the Windows group (assuming you have such, of course), and then create a user for the windows group, and then assign permissions directly to the individuals in that group:

create login [TK\Ekonomi] FROM Windows --Windows group

create database x

USE x

CREATE USER [TK\Ekonomi]

CREATE TABLE a(c1 int)
CREATE TABLE b(c1 int)

GRANT SELECT ON a TO [TK\Olle] --member of that windows group

EXECUTE AS LOGIN = 'TK\Olle'
SELECT * FROM a --OK
REVERT

---

Tibor Karaszi, SQL Server MVP (Web Blog)