LearnNowOnline

Blog

Windows Logins via Transact-SQL

Excerpt by Don Kiely | May 23, 2013

Transact-SQL provides full support for creating logins by using the CREATE LOGIN statement. The following example grants login privileges on SQL Server to the Windows user JaneAppDev on a machine named Willow (you'll need to change the machine name if you want to try the code yourself). The brackets around the Windows login are required.

CREATE LOGIN [WILLOWJaneAppDev] FROM WINDOWS;

GO

This statement adds the login but does not provide database access. You do that by using the CREATE USER statement. To add Jane to the AdventureWorks2012 database and make Production her default schema, add the following code:

USE AdventureWorks2012;

GO

-- Name the user the same name as login

CREATE USER [WILLOWJaneAppDev]

FOR LOGIN [WILLOWJaneAppDev]

WITH DEFAULT_SCHEMA = Production;

GO

You don't have to name the database user the same as the server login. For example, you could instead name the user Jane in the database, assuming that it does not already contain a user Jane:

DROP USER [WILLOWJaneAppDev];

GO

CREATE USER Jane FOR LOGIN [WILLOWJaneAppDev];

GO

In this case, because the default schema wasn't changed in the CREATE USER statement, the default would be dbo.

You can change a user account with the ALTER USER statement. For example, if you later wanted to make Jane's default schema Production, you could use this code:

ALTER USER Jane

WITH DEFAULT_SCHEMA = Production;

GO

TIP: Only one user in any given database can be mapped to a single Windows login. A single login may, however, be mapped to users in many databases on the same SQL Server instance.

If you add one of the built-in Windows groups to SQL Server (Users, Power Users, etc.) use BUILTIN in the Windows login name instead of the machine or domain name. The following example grants login privileges to anyone in the Windows Users group. This means that anyone who can log in to Windows doesn't have to log in a second time to get into SQL Server.

CREATE LOGIN [BUILTINUsers] FROM WINDOWS;

GO

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.