I want to create a user in the database. And I am using this stored procedure to create it:
CREATE PROCEDURE uspCreateUser
@Name varchar(50)
AS
BEGIN
CREATE USER [@Name]
WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
END
But when I execute my stored procedure with
EXEC uspCreateUser 'anny'
this creates user with @Name in database.
Can you please tell me how can I fix this?
This screenshot can help you to understand the question
You can use dynamic SQL. Generally, wherever the T-SQL syntax does not accept variable, dynamic SQL can provide the solution.
CREATE PROCEDURE uspCreateUser
@Name varchar(50)
AS
BEGIN
DECLARE @sqlstr nvarchar(max) = 'CREATE USER '+ @Name
+' WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]'
EXECUTE sp_executesql @sqlstr
END
GO
EXEC uspCreateUser 'anny';
You can find more information at:
http://www.sqlservercentral.com/Forums/Topic497615-359-1.aspx
