Windows Logins via Transact-SQL

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.

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:

 

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:

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:

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.

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 is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *