---------------------------------------------------------------------
--创建新的登录名
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/,如需转载,请注明出处,否则将追究法律责任。