The CREATE LOGIN statement in SQL Server is used to create a new login for SQL Server authentication. This login allows users to connect to the SQL Server instance. Here's the basic syntax for creating a SQL Server login:
CREATE LOGIN [login_name]
WITH
PASSWORD = 'your_password',
DEFAULT_DATABASE = [database_name],
CHECK_EXPIRATION = ON | OFF,
CHECK_POLICY = ON | OFF,
SID = 'your_sid';
Parameters:
- login_name: The name of the login you want to create. It must be unique within the SQL Server instance.
- PASSWORD: The password associated with the login. This must meet the password policy regulations if CHECK_POLICY is set to ON.
- DEFAULT_DATABASE: The database that the login will access by default when they connect.
- CHECK_EXPIRATION: Specifies whether the password should expire based on the server's password expiration policy.
- CHECK_POLICY: Specifies whether the password must meet the server's password complexity policy.
- SID: The security identifier for the login. This is optional; if not provided, SQL Server will generate one automatically.
Example:
Here's an example that demonstrates how to use the CREATE LOGIN statement:
CREATE LOGIN MyUser
WITH
PASSWORD = 'StrongP@ssw0rd!',
DEFAULT_DATABASE = MyDatabase,
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON;
Notes:
1. Always ensure that the password meets the complexity requirements if CHECK_POLICY is enabled.
2. After creating a login, you typically need to grant that login permissions to access specific databases or to perform specific actions.
3. You may also create logins for Windows users or groups using the FROM WINDOWS clause, like so:
CREATE LOGIN [DOMAIN\User] FROM WINDOWS;
After creating the login, you can further configure it by assigning it to specific roles or granting permissions in the database using the ALTER USER or GRANT statements.