sql server 创建备用管理员和只读用户

---------------------------------------------------------------------
--创建新的登录名
use master
go
exec sp_addlogin @loginame = 'mysa' , @passwd = '11'
go
exec sp_addlogin @loginame = 'read' , @passwd = '11'
go

--修改密码
--exec sp_password @old = null , @new ='sa' , @loginame ='sa'
--exec sp_password @old = null , @new ='11' , @loginame ='mysa'
--exec sp_password @old = null , @new ='11' , @loginame ='read'
---------------------------------------------------------------------
--服务器角色
--使用户属于固定服务器角色sysadmin
use master
go
exec sp_addsrvrolemember @loginame ='mysa',@rolename ='sysadmin'  --固定服务器角色
go
---------------------------------------------------------------------
--数据库角色
--在当前数据库创建新的 Microsoft SQL Server 角色
use master
go
exec sp_addrole @rolename = 'SelectExec',@ownername = 'dbo'
go
--使用户属于固定数据库角色db_datareader
exec sp_grantdbaccess @loginame = 'read'
go

--使用户read只能执行select语句，不能执行into,create,delete,drop等语句，
--用read用户查询数据库比较安全
exec sp_addrolemember @rolename = 'db_datareader' , @membername = 'read'
go


exec sp_addrolemember @rolename = 'SelectExec' , @membername = 'read'
go
---------------------------------------------------------------------
---------------------------------------------------------------------
--数据库角色
use northwind
go
-- 在当前数据库创建新的 Microsoft SQL Server 角色
exec sp_addrole @rolename = 'SelectExec',@ownername = 'dbo'
go
--使用户属于固定数据库角色db_datareader
exec sp_grantdbaccess @loginame = 'read'
go
exec sp_addrolemember @rolename = 'db_datareader' , @membername = 'read'
go
exec sp_addrolemember @rolename = 'SelectExec' , @membername = 'read'
go
----------------------------------------------------------------------
--数据库角色
use pubs
go
-- 在当前数据库创建新的 Microsoft SQL Server 角色
exec sp_addrole @rolename = 'SelectExec',@ownername = 'dbo'
go
--使用户属于固定数据库角色db_datareader
exec sp_grantdbaccess @loginame = 'read'
go
exec sp_addrolemember @rolename = 'db_datareader' , @membername = 'read'
go
exec sp_addrolemember @rolename = 'SelectExec' , @membername = 'read'
go
----------------------------------------------------------------------
----------------------------------------------------------------------
/*******************
--修改密码
--exec sp_password @old = null , @new ='sa' , @loginame ='sa'
--exec sp_password @old = null , @new ='11' , @loginame ='mysa'
--exec sp_password @old = null , @new ='11' , @loginame ='read'

-- 查看SQL-server登录名
Use master
Select name,Password,dbname from syslogins
Select * from syslogins

-- 显示数据库用户名
use master
exec sp_helpuser

--更改用户的默认数据库
exec sp_defaultdb @loginame = 'sa' ,  @defdb = 'master'
exec sp_defaultdb @loginame = 'mysa' ,  @defdb = 'master'
exec sp_defaultdb @loginame = 'read' ,  @defdb = 'master'

-- 数据库的对象权限
-- 使角色SelectExec可以执行存储过程
use pubs
grant exec on zuc_pr_plu8VSplu13 to SelectExec
grant exec on zuc_pr_MerchandiseManageAttribute to SelectExec
grant exec on zuc_pr_DM_SalequantitySaleamtDmsEndquantity to SelectExec
grant exec on zuc_pr_Duiduan_SalequantitySaleamtDmsEndquantity to SelectExec
grant exec on zuc_pr_jiajifenORjianjifen to SelectExec
grant exec on zuc_pr_Endquantity_0_Sale_Order_Chkreceive  to SelectExec
-- 撤消
-- revoke exec on zuc_pro_plu8VSplu13 from SelectExec

--经常检查带有空密码的帐户
Use master
Select name,
Password
from syslogins
where password is null
order by name

--检查所有不需要'sa'权限的存储过程和扩展存储过程访问权限：
Use master
Select sysobjects.name
From sysobjects, sysprotects
Where sysprotects.uid = 0
AND xtype IN ('X','P')
AND sysobjects.id = sysprotects.id
Order by name

********************/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10315043/viewspace-498413/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10315043/viewspace-498413/