Blog

Windows Logins via Transact-SQL

Excerpt by Don Kiely

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

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



Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.


This course excerpt was originally posted May 23, 2013 from the online courseware SQL Server 2012, Part 5 of 9: Security Basics by Don Kiely

SQL Server