Creating a Login
New: 14 April 2006
To access the Database Engine, users require a login. The login can represent the user's identity as a Windows account or as a member of a Windows group, or the login can be a SQL Server login that exists only in SQL Server. Whenever possible you should use Windows Authentication.
By default, administrators on your computer have full access to SQL Server. For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer. To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server. The following instructions are for Windows XP Professional.
To create a new Windows account
Click Start, click Run, in the Open box, type %SystemRoot%\system32\compmgmt.msc /s, and then click OK to open the Computer Management program.
Under System Tools, expand Local Users and Groups, right-click Users, and then click New User.
In the User name box type Mary.
In the Password and Confirm password box, type a strong password, and then click Create to create a new local Windows user.
To create a login
In a Query Editor window of SQL Server Management Studio, type and execute the following code replacing
computer_name
with the name of your computer.FROM WINDOWS
indicates that Windows will authenticate the user. The optionalDEFAULT_DATABASE
argument connectsMary
to theTestData
database, unless her connection string indicates another database. This statement introduces the semicolon as an optional termination for a Transact-SQL statement.CREATE LOGIN [computer_name\Mary] FROM WINDOWS WITH DEFAULT_DATABASE = [TestData]; GO
This authorizes a user name
Mary
, authenticated by your computer, to access this instance of SQL Server. If there is more than one instance of SQL Server 2005 on the computer, you must create the login on each instance thatMary
must access.Note
Because
Mary
is not a domain account, this user name can only be authenticated on this computer.
Next Task in Lesson
See Also
Other Resources
CREATE LOGIN (Transact-SQL)
Authentication Mode