How to work with Database Users – Database Security

Each database maintains a list of the users that are authorized to access that database. This list is distinct from the list of login IDs that’s maintained by the server. Code below presents the SQL statements you use to maintain the list of users for a database.

You use the CREATE USER statement to create a database user. On this statement, you code the name of the user, which is usually the same as the login name. In that case, you don’t need to specify the login name. This is illustrated in the first below. If you want to use a user name that’s different from the login name, however, you can include the FOR LOGIN clause to specify the login name that the user name is mapped to.

In most cases, it’s not a good idea to use two different names for the same user. For this reason, the FOR LOGIN clause is generally omitted. However, since login IDs generated from Windows user names include the domain name, those login IDs can be quite long. If all of your users are on the same Windows domain, then, you may want to use just the user names for the database users. This is illustrated in the second example, which creates a database user named AnneRoberts for the login ID Accounting\AnneRoberts.

You can also specify a default schema for a database user as illustrated in the third example. Then, when SQL Server searches for an object for that user, it will look for the object in the user’s default schema before it looks in the dbo schema.

After you create a database user, the user can set the database as the current database using the USE statement but can’t perform any operations on the database or the objects it contains. To do that, the user must be granted object and database permissions. You’ll learn how to grant these permissions in a moment.

If you need to change a database user, you can use the ALTER USER statement. This statement lets you change the user name or the default schema for the user. The fourth statement shown below, for example, changes the name of a database user from AnneRoberts to AnneStanley, and the fifth statement changes the default schema for a user to Marketing.

Finally, if you need to delete a database user, you use the DROP USER statement as illustrated in the last example. The only information you specify on this statement is the user name.

Note that all three of these statements work with the current database. For this reason, you must be sure to change the database context to the database you want to work with before you execute any of these statements. If you don’t, you may inadvertently create, change, or delete a user in the wrong database.

The syntax of the CREATE USER statement

 

The syntax of the ALTER USER statement

 

The syntax of the DROP USER statement

 

Statements that work with database users

A statement that creates a database user with the same name as a login ID

A statement that creates a database user for a Windows user account

A statement that creates a database user and assigns a default schema

A statement that changes a user name

A statement that assigns a default schema to a user

A statement that deletes a database user

 

Description

  • You use the CREATE USER statement to create a user for a login ID for the current database. If the login name is the same as the user name, you can omit the FOR LOGIN clause.
  • When you create a database user, you can specify a default schema. Then, SQL Server will look in this schema when it searches for objects for the database user before it looks in the default schema (dbo).
  • The ALTER USER statement lets you change the name of an existing database user or change the default schema for a user.
  • You use the DROP USER statement to delete a user from the current database.
  • Since all three of these statements work on the current database, you must change the database context using the USE statement before executing any of these statements.
  • The CREATE USER, ALTER USER, and DROP USER statements were introduced with SQL Server 2005.