just do it

SQL数据库中 的登陆,用户,角色 之间的关系

登陆:SQL Server2008中,有两种创建登录的方式:一种是Windows验证登录;另一种是SQL Server身份验证方式。“登录方式”就好比办公楼上使用不同类型的锁,一旦选择了使用什么类型的锁,就可以开始为每位员工配发钥匙,而这个钥匙就是 “登录”。或称“登录名”,但员工只有进入大楼的权利,但却没有权限查看和使用里面的资源(比如档案柜)。如果是sysadmin或 securityadmin固定服务器角色的成员,则可以创建两种类型的登录之一:标准登录(比如前面比喻中的金属钥匙)和Windows登录(比如较新 型的电子出入卡)。

用户:既然员工拥有了进入大楼的钥匙,就应该拥有适当的管理性访问权,他们需要通过访问其他资源来完成工作。例如,如果打算允许财务部访问账目文件,就需要给他们一把文件柜的钥匙。员工现在有两把钥匙:一把是打开大门的钥匙,一把是打开文件柜的钥匙。

同样,一旦用户已登录到了SQL Server上,就需要允许用户访问数据库。为此,需要创建数据库用户账户,然后给这些用户账户授予权限。一旦这个过程结束,SQL Server用户就拥有了多把钥匙:一把打开大门的钥匙(登录),以及一把打开他们需要访问的每个文件柜(数据库)的钥匙。

角色:会计需要签发公司支票,这个权限可以用两种方式之一来授予。第一,可以给每名会计分别提供一本从同一账户中支取的支票簿,并授予从该支票簿中开支票 的权限。在这种情况下,需要设法跟踪当月已经开出的全部支票,否则这种方法可能会造成可怕的后果。完成这项工作的最佳方法是让整个公司账户只使用一本支票 簿,并给所有会计授予从这本支票簿中开支票的一个组权限。

在SQL Server中,当几个用户需要访问数据库的权限时,比较容易的方法是将所有的权限作为一个组授给他们,而不是设法分别管理每个用户。

言归正传,先用存储过程实现以上操作:

–创建一个简单的登录,登录名为:newlogin;登录密码:123456;默认数据库:master,默认数据库也可以不指定。

EXEC sp_addlogin ‘newlogin’,’123456′,’master’
–创建用户

–创建一个简单的用户,如果不指定用户名,则添加到当前数据库登录名中,如果不指定角色,则该用户默认属于public角色。下为添加newlogin登录名。

EXEC sp_adduser ‘newlogin’
–创建一个带用户名的用户,用户可以与登录名相同(同上一种类似),也可以不同,但要设定当前登录名,用户角色可选,默认为public。下为将用户newuser添加到newlogin登录名中。

EXEC sp_adduser ‘newlogin’,’newuser’
–创建角色

EXEC sp_addrole ‘newrole’
–下为将用户下为将用户newuser添加到newlogin登录名中。并指定newrole角色。

EXEC sp_adduser ‘newlogin’,’newuser’,’newrole’
–为角色newrole赋予jobs表的所有权限

GRANT ALL ON jobs TO newrole
–为角色newrole赋予sales表的查、改权限

GRANT SELECT,UPDATE ON sales TO newrole
–禁止角色newrole使用employees表的插入权限

DENY INSERT ON employees TO newrole
另一种创建用户和赋予角色的方式

–为登录newlogin在数据库中添加安全账户newuser

EXEC sp_grantdbaccess ‘newlogin,’newuser’
–添加newuser为角色newrole的成员

EXEC sp_addrolemember ‘newrole’,’newuser’
–数据库用户、角色、登录的删除操作

–删除当前数据库用户

EXEC sp_revokedbaccess ‘newuser’;
–删除数据库登录

EXEC sp_droplogin ‘newlogin’
–删除数据库角色

EXEC sp_droprole ‘newrole’
–从数据库角色(newrole)中删除用户(newuser)

EXEC sp_droprolemember ‘newrole’, ‘newuser’
–用SQL代码新建登录、用户

–创建带密码的mylogin登录名,MUST_CHANGE 选项需要用户首次连接服务器时更改此密码。

CREATE LOGIN mylogin WITH PASSWORD = ‘123456’ MUST_CHANGE;
–创建映射到凭据的登录名。

–以下示例将创建mylogin登录名。此登录名将映射到mycredential凭据。

CREATE LOGIN mylogin WITH PASSWORD = ‘123456’, CREDENTIAL = mycredential;
–从Windows 域帐户创建登录名

–如果从Windows 域帐户映射登录名,则登录名必须用方括号([ ]) 括起来。

CREATE LOGIN [jack\xiangzhao] FROM WINDOWS;
–如果指定用户名,则不使用默认登录名作为该数据库用户

CREATE USER myuser FOR LOGIN mylogin
–以下示例将创建用户myuser拥有的数据库角色myrole

CREATE ROLE myrole AUTHORIZATION myuser;
–以下示例将创建db_role固定数据库角色拥有的数据库角色myrole

CREATE ROLE myrole AUTHORIZATION db_role;

点赞