创建用户的语句如何放到存储过程

Question

2008 r2 sp2

要求建立A库+A用户，A用户只能访问A库。语句已做好，怎么将语句放到存储过程像传参数那样方便创建

存储过程不能使用use ,但语句里很多权限是针对当前库

附语句

create DATABASE db4;
go
USE [master]
EXEC sp_addlogin 'user4', 'abc*123', 'db4';

USE [db4]
EXEC sp_adduser 'user4', 'user4' 
EXEC sp_addrolemember N'db_datareader', N'user4'
EXEC sp_addrolemember N'db_datawriter', N'user4'
GRANT EXECUTE TO user4 AS [dbo]

EXEC sp_dropuser 'user4'
ALTER AUTHORIZATION ON DATABASE::db4 TO user4

Answer 1

CREATE PROC xx
AS
create DATABASE db4;
EXEC master..sp_addlogin 'user4', 'abc*123', 'db4';
EXEC [db4]..sp_adduser 'user4', 'user4' 
EXEC [db4]..sp_addrolemember N'db_datareader', N'user4'
EXEC [db4]..sp_addrolemember N'db_datawriter', N'user4'
exec [db4]..sp_executesql N'GRANT EXECUTE TO user4 AS [dbo]'
EXEC [db4]..sp_dropuser 'user4'
ALTER AUTHORIZATION ON DATABASE::db4 TO user4
go

Answer 2

建议使用CREATE LOGIN /CREATE USER /sp_addrolemember,因为sp_addlogin 类似的存储过程可能会在将来移除。

http://technet.microsoft.com/zh-cn/library/ms173768.aspx

---

Please Mark As Answer if it is helpful.