Avatar of Computers4me
Computers4me
 asked on

How to create a User Account in Microsoft SQL Databse

Hi,
Can anyone explain how to creat A User Account in a SQL Database.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Alpesh Patel

8/22/2022 - Mon
Steve Wales

It's a two step process.

First you have to create a login to the instance.

That can be a SQL Server account (with a password) or you take take the Windows AD login and pass that through.

Then you create a user in the database connected to that login and grant appropriate privileges.

use master
go
create login [fred] from windows with default database=[master]
go
use demo
go
create user fred for login fred
go
grant select on table to fred

Open in new window


The user name and the login name do not have to be the same.  Make sure you grant object permissions to the user, not the login.

There are exceptions, for example you can make a login a system wide administrator for all databases on the instance.

From the documentation:

Creating a login: http://msdn.microsoft.com/en-us/library/aa337562.aspx
Creating a user: http://msdn.microsoft.com/en-us/library/ms173463.aspx
Ess Kay

you can also right click on the database, and use the graphic user interface
Computers4me

ASKER
How would you Pass through an AD User account?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Steve Wales

If my domain is MYDOMAIN and my account is sjwales it would be:

create login [MYDOMAIN\sjwales] from windows with default database=[master]

Open in new window


All of this can be done from the SSMS GUI as well as indicated by esskayb2d.

The username could then be anything, drop the domain from user if you want, for example.

create user sjwales for login [MYDOMAIN\sjwales]

Open in new window


and then assign appropriate permissions to sjwales as needed.
ASKER CERTIFIED SOLUTION
Alpesh Patel

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.