In SQL Server, to create a database user that is mapped to an existing SQL Server login, you can use the CREATE USER statement. This statement allows you to create a new user in a specific database and associate it with an existing SQL Server login.
Here's the general syntax for the CREATE USER statement:
CREATE USER [username] FOR LOGIN [loginname];
- [username]: The name you want to assign to the user within the database.
- [loginname]: The name of the SQL Server login that the user should be mapped to.
Example
Suppose you have a SQL Server login called myLogin and you want to create a user called myUser in the database myDatabase. You would run the following commands:
1. Ensure you are in the correct database context:
USE myDatabase;
GO
2. Create the user mapped to the existing login:
CREATE USER myUser FOR LOGIN myLogin;
GO
Additional Options
- You may also want to specify a default schema for the user by using the WITH DEFAULT_SCHEMA option. For example:
CREATE USER myUser FOR LOGIN myLogin WITH DEFAULT_SCHEMA = dbo;
GO
- You can verify the creation of the user by querying the system views:
SELECT * FROM sys.database_principals WHERE name = 'myUser';
Permissions
After creating the user, you might want to assign specific permissions or roles to that user based on what database operations they need to perform. You can do this using the ALTER ROLE statement. For instance, if you want to add myUser to the db_datareader role, you would execute:
ALTER ROLE db_datareader ADD MEMBER myUser;
GO
This allows you to manage user permissions effectively within your SQL Server database environment.