To create a database user that is mapped to a Windows login in SQL Server, you can use the CREATE USER statement. The user must be associated with an existing login. The syntax for creating a user in the context of a Windows login is as follows:
CREATE USER [UserName] FOR LOGIN [Domain\LoginName];
Here's a breakdown of the syntax:
- UserName: This is the name of the database user you want to create.
- Domain\LoginName: This represents the Windows login that the database user will be mapped to, where Domain is the Windows domain and LoginName is the username.
Example
Suppose you want to create a database user named SalesUser mapped to a Windows login called DOMAIN\JohnDoe. The SQL statement would look like this:
CREATE USER [SalesUser] FOR LOGIN [DOMAIN\JohnDoe];
Steps to Execute
1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. Open a new query window.
4. Ensure you are using the correct database where you want to create the user by executing:
USE [YourDatabaseName];
5. Execute the CREATE USER statement as shown above.
Additional Notes
- Make sure that the Windows login (DOMAIN\JohnDoe in this example) already exists on the SQL Server instance. If it does not exist, you need to create it using the CREATE LOGIN statement.
- After creating the user, you may want to assign roles or permissions to the new user according to your requirements. This can be done using the ALTER ROLE statement or by granting specific permissions directly.
Example of Granting Permission
To grant the user SalesUser the ability to select data from a table named SalesData, you could execute:
GRANT SELECT ON [dbo].[SalesData] TO [SalesUser];
Using these steps and examples, you can successfully create a database user mapped to a Windows login in SQL Server.