create login login1 with password='111111'  --创建一个login，用于登录
go 
use [BookDB]   --在数据库[BookDB]上创建用户user1，拥有下面三个权限
go
create user user1 for login login1 with default_schema=dbo
go
exec sp_addrolemember 'db_ddladmin','user1'
exec sp_addrolemember 'db_datareader','user1'
exec sp_addrolemember 'db_datawriter','user1'
go

use [BookLibrary]  --在数据库[BookLibrary]上创建用户user2，赋予下面三个权限
go
create user user2 for login login1 with default_schema=dbo
go
exec sp_addrolemember 'db_ddladmin','user2'
exec sp_addrolemember 'db_datareader','user2'
exec sp_addrolemember 'db_datawriter','user2'
go
use [BookLibrary]  --在数据库[BookLibrary]上创建用户user2，收回下面三个权限
go
exec sp_droprolemember 'db_ddladmin','user2'
exec sp_droprolemember 'db_datareader','user2'
exec sp_droprolemember 'db_datawriter','user2'
go

--上面复权的另一种写法
USE [BookLibrary]
GO
ALTER ROLE [db_ddladmin] add MEMBER [user2]
GO
ALTER ROLE [db_datareader] add MEMBER [user2]
GO
ALTER ROLE db_datawriter add MEMBER [user2]

--上面收权的另一种写法
USE [BookLibrary]
GO
ALTER ROLE [db_ddladmin] DROP MEMBER [user2]
GO
ALTER ROLE [db_datareader] DROP MEMBER [user2]
GO
ALTER ROLE db_datawriter DROP MEMBER [user2]
GO