我有一个脚本,如果存在,它会删除用户,然后重新创建它。我重复了30次。所 示:
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = N'johndoe')
BEGIN
PRINT 'Dropping user johndoe...'
DROP USER johndoe;
END
CREATE USER [johndoe] FOR LOGIN [uatjohndoe];
我想知道是否可以编写一个接受用户名和域名的存储过程,然后删除并重新创建用户?这样我就可以这样打电话:
EXEC usp_DropAndRecreateUsers @username='johndoe', @domainName='uat'
但是,在编写存储过程后,我意识到这并不像我想象的那么容易。这可能是尚未完成的原因。但是,这在SQL Server(2008及更高版本(中甚至可以做到吗?
我所做的是(这不起作用(:
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(@username varchar(100),
@domainName varchar(100))
AS
DECLARE @loginName varchar(200) = '['+@domainName+''+@username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = @username)
BEGIN
DROP USER @username;
END
CREATE USER @username FOR LOGIN @loginName;
更新:错误是当我在Drop/Create User @username
中使用参数时,它被认为是无效的sql语句。错误消息为:Incorrect syntax near '@username'.Expecting ID, or QUOTED_ID.
不是从变量运行,而是创建查询并执行它
CREATE PROCEDURE dbo.USP_DROPANDRECREATEUSERS
(@username varchar(100),
@domainName varchar(100))
AS
BEGIN
DECLARE @loginName varchar(200) = '['+@domainName+''+@username+']';
DECLARE @query VARCHAR(MAX);
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = @username)
BEGIN
SET @query = 'DROP USER ' + @username;
EXEC (@query);
END
SET @query = 'CREATE USER ' + @username + ' FOR LOGIN ' + @loginName;
EXEC (@query);
END