How to set the user rights by passing the database name as Input parameter in a stored procedure?

How to set the user rights by passing the database name as Input parameter in a stored procedure?

Declare @username nvarchar(100)
Declare @databasename nvarchar(100)

set @username = 'ABC'

CREATE USER @username FOR LOGIN @username
ALTER ROLE [db_datareader] ADD Member @username

Answer

Sean has mentioned the security concerns in the comments, however, as I mentioned, you’ll need dynamic SQL for this. In my opinion the most important thing to make sure you do, when using Dynamic SQL, is properly quoting your strings, to ensure that the exposure to Injection is minimised. With what you’re doing here, there are other problems anyway; but like I said, Sean covered that.

Anyway, this is how you’d do it:

DECLARE @username sysname;
SET @username = N'ABC';

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'CREATE USER ' + QUOTENAME(@Username) + N' FOR LOGIN ' + QUOTENAME(@Username) N';' + NCHAR(10) +
           N'ALTER ROLE db_datareader ADD MEMBER' + QUOTENAME(@Username) + N';';
PRINT @SQL; --Your "best" friend
EXEC sp_executesql @SQL;

If you really need to, you can make this reference a database as well in the same statement, however, that really does seem like a bad idea.