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

本文主要是介绍sqlserver创建login、user并授予相应数据库的权限，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

这篇关于sqlserver创建login、user并授予相应数据库的权限的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---