How Can I Securely Create SQL Logins Using Parameters in Stored Procedures?

Creating SQL Login with Parameters in Stored Procedures

In an effort to automate tenant creation within a SaaS database, a developer attempted to utilize a stored procedure with parameterized inputs for username and password in the CREATE LOGIN statement. However, they encountered cryptic error messages related to incorrect syntax.

The root cause of the issue lies in the CREATE LOGIN syntax, which requires literal values rather than parameters. To resolve this, a workaround exists:

1. Wrap the CREATE LOGIN command within an EXEC statement.
2. Construct the CREATE LOGIN command dynamically as a string variable.
3. Leverage QUOTENAME() to safeguard against potential SQL injection attacks.

Here's a revised version of the stored procedure that incorporates these changes:

CREATE PROCEDURE [MyScheme].[Tenants_InsertTenant]
    @username nvarchar(2048),
    @password nvarchar(2048)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

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

By implementing this workaround, the stored procedure can now dynamically create SQL logins based on provided parameters, enabling the tenant creation process to become fully automated.

The above is the detailed content of How Can I Securely Create SQL Logins Using Parameters in Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!