Sql-server – Can we create multiple users in one login in SQL Server 2008

loginssql serversql-server-2008users

I have one database where I want 4 users in that particular database, so do I have to create 4 logins, one for each user ?

I want multiple users from one login. I tried to create a second user by using the same login but I am getting error

The login already has an account under a different user name

Best Answer

If you are using SQL Authentication, then the short answer to your first question (do I have to create 4 logins, one for each user?) is, Yes. You do.

It sounds like you're looking to group individual users under a single entity.

If you are able to use Windows Authentication then you can create an Active Directory Group, let's call it GP-DatabaseUsers. Add the Windows logins as members of that group in AD, and in SQL add the group as a user and attribute the desired credentials to that group. You can still track individual login usage, but you'll be controlling security of a single entity.

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';

Sql-server – How to create database, and add user linked to login

Not sure how dbo is coming into play. Here is how you create a user in a database mapped to a server login, whether that login is already associated to a user in any other database:

CREATE LOGIN foo WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER foo FROM LOGIN [foo];
GO
-- then permissions:
GRANT ... TO foo;
EXEC sp_addrolemember N'<role>', N'foo';
etc.

Best Answer

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

Sql-server – How to create database, and add user linked to login

Related Question