用SQL建用户和赋权限

http://blog.csdn.net/hui_0924/article/details/5877981

在SQL Server中有三种特殊的用户：系统管理员、用户数据库所有者（建立相应数据库的数据库用户）DBO、一般用户。系统管理员对整个系统有操作权；用户数据库所有者对他所建立的数据库具有全部操作权利；一般用户对给定的数据库只有被授权的操作权限。 
数据库用户一般可分为用户组，任一数据库在建立后即被赋予一个用户组public。

1、建立SQL服务器用户名（登录帐号） 
作为一个用户，为了使用SQL Server，他必须在SQL Server上有一个SQL服务器用户名（登录帐号）。这个帐号是系统管理员通过sp_addlogin来增加的。 
sp_addlogin 登录名，口令，库名，语言，全名

例：建立用户a, 口令为123456(最低六位)，全名为aaa 
1> sp_addlogin a,test,null,null,aaa 
2> go 
3> select * from syslogins 
4> go

2、增加数据库用户名 
同样，为了使用SQL Server上的数据库，他必须在数据库中有一个用户名，这个用户名是数据库所有者通过sp_adduser来增加的。数据库用户名不必和用户帐户一样，而且在不同的数据库中用户名可以不同。多个用户可以有相同的SQL Server帐户。同样，多个SQL Server帐户可以有相同的数据库名。 
sp_adduser 登录名，别名，组名 
登录名为用户的SQL服务器登录名；别名为用户在数据库中的用户名；组名为用户所属的数据库用户组。

例：用户a增加为test的用户，别名为a1,属于用户组china

1>sp_addgroup china 
2>go

1>sp_adduser a,a1,china 
2>go

1> sp_helpuser 
2> go

*例：删除别名a1,用户组china,登录名a

1> use test 
2> go 
3> sp_dropuser a1 
4> go 
5> sp_helpuser 
6> go 
1>sp_dropgroup china 
2>go 
1> sp_helpgroup 
2> go 
3> use master 
4> go 
5> sp_droplogin a 
6> go 
7> select * from syslogins 
8> go

3、数据库操作授权

grant 命令序列 to 用户名

A. 系统管理员可以授予其他用户CREATE DATABASE的权限，使其他用户可以成为数据库所有者。

B. 数据库所有者在他所拥有的数据库中，可以授予其他用户的权限有：

l CREATE TABLE ------------------- 建表 
lCREATE DEFAULT ------------------ 建缺省 
lCREATE RULE ------------------- 建规则 
lCREATE PROCedure ------------------ 建存储过程 
lCREATE VIEW ------------------- 建视图 
lDUMP DATABASE ------------------- 备份数据库 
lDUMP TRANsaction ------------------ 备份日志

C. 数据库对象所有者可以授予其他用户的操作权限有：

l SELECT 
l UPDATE 
l INSERT 
l EXECute 
l DELETE 
l REFERENCE

例：授予a1在数据库test上建表，建视图，建存储过程;对表students有select,reference权；对name,native字段有update权。

1> use test 
2> go 
3> grant creat table,create procedure ,create view to a1 
4> go 
5> grant select,reference on students to a1 with grant option 
6> go 
7> grant update on students(name,native) to a1 
8> go 
9> sp_helprotect 
10> go

固定服务器角色 描述 
sysadmin 可以在 SQL Server 中执行任何活动。 
serveradmin 可以设置服务器范围的配置选项，关闭服务器。 
setupadmin 可以管理链接服务器和启动过程。 
securityadmin 可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码。 
processadmin 可以管理在 SQL Server 中运行的进程。 
dbcreator 可以创建、更改和除去数据库。 
diskadmin 可以管理磁盘文件。 
bulkadmin 可以执行 BULK INSERT 语句。

固定数据库角色 描述 
db_owner 在数据库中有全部权限。 
db_accessadmin 可以添加或删除用户 ID。 
db_securityadmin 可以管理全部权限、对象所有权、角色和角色成员资格。 
db_ddladmin 可以发出 ALL DDL，但不能发出 GRANT、REVOKE 或 DENY 语句。 
db_backupoperator 可以发出 DBCC、CHECKPOINT 和 BACKUP 语句。 
db_datareader 可以选择数据库内任何用户表中的所有数据。 
db_datawriter 可以更改数据库内任何用户表中的所有数据。 
db_denydatareader 不能选择数据库内任何用户表中的任何数据。 
db_denydatawriter 不能更改数据库内任何用户表中的任何数据。

sp_addrolemember 'public','username' --赋予username public的权限

也可以先建一个角色:
sp_addrole 'rolename' --并赋予这个角色读哪些表的权限
sp_addrolememver 'rolename','username'

--创建新的 SQL Server 登录，使用户得以连接使用 SQL Server 身份验证的 SQL Server。
EXEC sp_addlogin @loginame = '', @passwd = '', @defdb = '', @deflanguage = NULL, @sid = NULL, @encryptopt = NULL
--使 Windows NT 用户或组帐户得以使用 Windows 身份验证连接到 SQL Server。
EXEC sp_grantlogin @loginame = ''

--删除 SQL Server 登录，以阻止使用该登录名访问 SQL Server。
EXEC sp_droplogin @loginame = ''
--阻止 Windows NT 用户或组连接到 SQL Server。
EXEC sp_denylogin @loginame = '' 
--从 SQL Server 中删除用 sp_grantlogin 或 sp_denylogin 创建的 Windows NT 用户或组的登录项。
EXEC sp_revokelogin @loginame = ''

--更改登录的默认数据库。
EXEC sp_defaultdb @loginame = '', @defdb = ''
--更改登录的默认语言。
EXEC sp_defaultlanguage @loginame = '', @language = ''
--添加或更改 SQL Server 登录密码。
EXEC sp_password @old = '', @new = '', @loginame = ''

--添加服务器角色新成员。
EXEC sp_addsrvrolemember @loginame = '', @rolename = ''
--添加服务器角色某成员。
EXEC sp_dropsrvrolemember @loginame = '' , @rolename = ''

--为 SQL Server 登录或 Windows NT 用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限（授予默认的“public”数据库角色）。
EXEC sp_grantdbaccess @loginame = '', @name_in_db = NULL
--或
EXEC sp_adduser @loginame = '', @name_in_db = NULL, @grpname = ''

--从当前数据库中删除安全帐户。
EXEC sp_revokedbaccess @name_in_db = ''
--或
EXEC sp_dropuser @name_in_db = ''

--在当前数据库创建新数据库角色。
EXEC sp_addrole @rolename = '', @ownername = ''
--在当前数据库删除某数据库角色。
EXEC sp_droprole @rolename = ''

--在当前数据库中添加数据库角色新成员。
EXEC sp_addrolemember @rolename = '', @membername = ''
--在当前数据库中删除数据库角色某成员。
EXEC sp_droprolemember @rolename = '', @membername = ''

--权限分配给数据库角色、表、存储过程等对象
--1、授权访问
GRANT
--2、拒绝访问
DENY
--3、取消授权或拒绝
REVOKE
--4、Sample(pubs):
GRANT SELECT ON authors TO Limperator
DENY SELECT ON authors TO Limperator
REVOKE SELECT ON authors TO Limperator

use test
exec sp_addlogin 'a','12345','test'
exec sp_adduser 'a','a','public'
EXEC sp_addrolemember 'db_owner','a'