SQL Server Performance Forum – Threads Archive

Do I need 7 logins for creating 7 users

Hi!All
I am a new guy in this forum.
I have created a login using EM.Now I want to create 7 users for a database.and assign all of them db_datareader and db_datawriter fixed database roles,so that they cannot create any objects on database but can only read and write.
Does I have to create 7 logins for creating 7 users.
If not Please explain the method of creating the users without creating 7 logins

---

Hi,<br />BOL says :<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Create a login ID and default database<br />This example creates a SQL Server login for the user Albert, with a password of "B1r12-36" and a default database of corporate.<br /><br />EXEC sp_addlogin ‘Albert’, ‘B1r12-36’, ‘corporate'<br /><br />then<br /><br />sp_addrolemember to add roles to newly added user<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><b>refer sp_addlogin in BOL</b><br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />

---

Does this mean that for having 7 users on my db I have to create 7 logins using sp_addlogin

---

yes you have to create 7 logins if your requirement is for 7 users.
regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

---

opps!
But this sounds quite fascinating..users are database specific ..logins are server specifics..
even then MS didnt made any provision for these fact

---

True, this is required if you want to assign different permission to the individual user. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

You could create a single ‘SQL Authentication’ login, and have the 7 users share that login. This wouldnt work for Windows Authenticated logins. Also it is often better to assign users to a role, and apply permissions to the role instead of at user level

---

I have created a login ‘testlogin’ for db ‘test’ using EM Now when I tried to create the users
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user1’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user2’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user3’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user4’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user5’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user6’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user7’ , @grpname = ‘db_datawriter’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user1’ , @grpname = ‘db_datareader’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user2’ , @grpname = ‘db_datareader’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user3’ , @grpname = ‘db_datareader’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user4’ , @grpname = ‘db_datareader’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user5’ , @grpname = ‘db_datareader’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user6’ , @grpname = ‘db_datareader’
go
sp_adduser @loginame = ‘testlogin’ , @name_in_db = ‘user7’ , @grpname = ‘db_datareader’
go I am getting the error as
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 127
User or role ‘user1’ already exists in the current database.
and so on for all users

---

No need to use SP_ADDUSER again, BOL refers
To add a member to a SQL Server database role Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which the role exists.
Click Roles.
In the details pane, right-click the role to which the user will be added, and then click Properties.
Click Add, and then click a user or users to add.
Only users in the selected database can be added to the role.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Tried in the same way..got a info message box saying "There are no more users to be added" This arise because I havent created users.
How can I create user1,user2,user3,user4,user5,user6,user7 and mapped all these users to common login ‘testlogin’.
testlogin is already created.

---

hi,
have you tried as satya suggested , seems you are creating user instead of maping it to role Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

---

I m following the way as told by Chappy i.e "You could create a single ‘SQL Authentication’ login, and have the 7 users share that login"

---

From BOL, under Users > described:

---

So can I conclude that a login id cannot be associated with more than one userid in same database

---

Yes, one login (server level) can only be associated to one user (database level) in any single database. But different logins can be associated to a single user in any single database. You can create a user in the database only for a given valid login, but this login can have a different name than the one you’re entering for the user. Confusing?

---

You haven’t stated the environment, but generally you should avoid using SQL Logins. To use windows authentication: Create a global group, add the users as members
Create a local group on the SQL server, add the global group as a member
Grant login to the local group (use sp_grantlogin)
Grant the local group access to the database (sp_addrolemember)

---

]]>