sqlserver创建login、user并授予相应数据库的权限

发布于:2024-05-02 ⋅ 阅读:(143) ⋅ 点赞:(0)

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


网站公告

今日签到

点亮在社区的每一天
去签到