How Can I Dynamically Create SQL Logins with Variable Usernames?

Unable to Create LOGIN with Variable Username

Justin, a developer seeking assistance in creating a stored procedure, faces the challenge of establishing a SQL login for tenants. However, his initial attempt using parameters results in the following errors:

• Incorrect syntax near '@username'
• Incorrect syntax near the keyword 'with'

Solution

CREATE LOGIN, unfortunately, does not allow the use of variables. A workaround is to embed the login creation statement in a dynamic SQL string:

DECLARE @sql nvarchar(max) = 'CREATE LOGIN ' + quotename(@username) + ' WITH PASSWORD = ' + quotename(@password, '''');
EXEC(@sql);

Note: Quotename is added for protection against SQL injection attacks.

The above is the detailed content of How Can I Dynamically Create SQL Logins with Variable Usernames?. For more information, please follow other related articles on the PHP Chinese website!