34,838
社区成员




--该过程创建了一个LOGIN和USER
create procedure sp_CreatePrincipal
@name varchar(256),
@password varchar(128)
as
declare @sqlcmd varchar(2000)
begin tran
-- create LOGIN
set @sqlcmd = 'create login ' + quotename(@name) + ' with password = ' + quotename(@password, '''')
exec (@sqlcmd)
if @@error <> 0
begin
rollback tran
print 'Cannot create login'
return
end
-- create USER
set @sqlcmd = 'create user ' + quotename(@name)
exec (@sqlcmd)
if @@error <> 0
begin
rollback tran
print 'Cannot create user'
return
end
commit tran
go
--创建用户alice,密码1234
exec sp_CreatePrincipal 'alice', '1234'
--在用户alice下,创建新用户bob
execute as login = 'alice'
sp_CreatePrincipal 'bob', '1234'
revert