创建一个只读的登录名/用户名

--1. 新建登录名
USE [master]
GO
if not exists(select * from sys.server_principals 
              where name = 'r7')
	BEGIN
		PRINT '不存在此登录名'
		CREATE LOGIN r7 WITH PASSWORD=N'r7', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
	END
ELSE
	PRINT '已存在此登录名'
GO
--2. 在指定库新建用户名
USE [db_Study]
GO
if not exists(select * from sys.database_principals
              where name = 'r7')
	BEGIN
		PRINT '不存在此用户'
		CREATE USER r7 FOR LOGIN r7
	END
ELSE
	PRINT '已存在此用户'
GO
--3. 将只读权限给新建用户名
USE [db_Study]
GO
if not exists(
                select *
                from sys.database_role_members rm  
                inner join sys.database_principals dp  
                        on dp.principal_id = rm.role_principal_id  
                   
                inner join sys.database_principals dpp  
                        on dpp.principal_id = rm.member_principal_id 
                where dp.name = 'db_datareader' and dpp.name = 'r7'
	)
	BEGIN
		PRINT '不存在只读权限'
		EXEC sp_addrolemember N'db_datareader', N'r7'	
	END
ELSE
	PRINT '已存在只读权限'
GO