sqlserver2008新增只有查询查询权限的账号

—————–新增——————————
—新增用户xiebo,密码为xiebo123 ; 针对数据库MG_IOVDB_V6只给查询权限

–新增用户
USE [master]
GO
CREATE LOGIN [xiebo] WITH PASSWORD=N’xiebo123′, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

use [MG_IOVDB_V6]
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’xiebo’)
DROP USER [xiebo]
GO
CREATE USER [xiebo] FOR LOGIN [xiebo] –资料库可读权限
GO
EXEC sp_addrolemember N’db_datareader’, N’xiebo’
GO

 

 

—————-删除————————————-
—删除登录名xiebo; 删除针对数据库MG_IOVDB_V6的用户xiebo
USE [master]
GO
DROP LOGIN [xiebo]
GO
–删除指定资料库中的用户
USE [MG_IOVDB_V6]
GO
DROP USER [xiebo]
GO

 

 

 

 

===================================================================

===================================================================
–新增用户
USE [master]
GO
CREATE LOGIN [reader] WITH PASSWORD=N’123456abcdef’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
–ALTER TRACE 是用在SQL Server Profiler运行权限。仅在master中执行一次即可。
USE [master]
GO
GRANT ALTER TRACE TO[reader]
GO
–查看进程权限
GRANT VIEW SERVER STATE TO [reader]
GO

USE [DATABASE] –在指定的资料库中执行

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’reader’)
DROP USER [reader]
GO
CREATE USER [reader] FOR LOGIN [reader] –资料库可读权限
GO
EXEC sp_addrolemember N’db_datareader’, N’reader’
GO
GRANT DELETE ON SCHEMA::[dbo] TO [reader] –数据表数据删除
GO
GRANT UPDATE ON SCHEMA::[dbo] TO [reader] –数据表数据更新
GO
GRANT INSERT ON SCHEMA::[dbo] TO [reader] –数据表数据插入
GO
GRANT EXECUTE ON SCHEMA::[dbo] TO [reader] –exec执行存储过程
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [reader] –查看视图、存储过程脚本定义内容
GO
GRANT ALTER ON SCHEMA::[dbo] TO [reader] –对象更改ALTER、卸载DROP
GO
GRANT CREATE VIEW TO [reader] –创建视图
GO
GRANT CREATE PROCEDURE TO [reader] –创建存储过程
GO

–取消 ALTER TRACE权限
USE [master]
GO
REVOKE ALTER TRACE TO[reader]
GO
–取消 查看进程权限
REVOKE VIEW SERVER STATE TO [reader]
GO

USE [DATABASE]
GO
REVOKE DELETE ON SCHEMA::[dbo] TO [reader] –取消数据表数据删除
GO
REVOKE UPDATE ON SCHEMA::[dbo] TO [reader] –取消数据表数据更新
GO
REVOKE INSERT ON SCHEMA::[dbo] TO [reader] –取消数据表数据插入
GO
REVOKE EXECUTE ON SCHEMA::[dbo] TO [reader] –取消exec执行存储过程
GO
REVOKE VIEW DEFINITION ON SCHEMA::[dbo] TO [reader] –取消查看视图、存储过程脚本定义内容
GO
REVOKE ALTER ON SCHEMA::[dbo] TO [reader] –取消对象更改ALTER、卸载DROP
GO
REVOKE CREATE VIEW TO [reader] –取消创建视图
GO
REVOKE CREATE PROCEDURE TO [reader] –取消创建存储过程
GO

–删除登入用户
USE [master]
GO
DROP LOGIN [reader]
GO
–删除指定资料库中的用户
USE [DATABASE]
GO
DROP USER [reader]
GO
————————————————
版权声明:本文为CSDN博主「231bobo」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/bobo231lele/article/details/51127542

原文链接:,转发请注明来源落伍老站长!

发表评论