关于SQLSERVER 登陆认证身份和认证方式的总结

admin

关于SQLSERVER 登陆认证身份和认证方式的总结

---

在Access使用链接表或ADO方式，以及早期的ADP方式都可以连接Sql Server数据据
初学者总是对Sql server用户安全理解不透，在连接Sql server时总是会出现这样或那样的错误
这里列一下Sql server登录身份的相关知识，给有需要的网友参考。
内容写得非常详细。

作者：StevenZ

1（一）概述:

一、安全管理：
1）对用户登陆进行身份认证Authentication：当用户登陆到数据库系统时，系统对该用户的账号和口令进行认证，确认账号是否有效以及能否访问数据库系统。
2）对用户操作进行权限的控制，让用户只能在允许的权限内操作数据库。
用户操作数据库的权限：
1）登陆SQLSERVER服务器必须通过身份验证；
2）必须是该数据库的用户，或者是某一数据库角色的成员；
3）必须有执行权限。

二、身份认证：
1、WindowsNT认证模式（Windows集成身份认证模式）
登陆WindowsNT时身份认证，只要正常登陆系统，在登陆时选择“集成WINDOWS身份验证“，登陆SQL时就不用再进行身份认证。
  1)要采用NT网络账号登陆SQLSERVER，必须将NT网络账号加入到SQL中；
  2)NT网络账号登陆到另外一个网络的SQLSERVER，必须在NT网络中设置彼此的托管权限。

这主要是从服务器的登陆就开始着手安全性登陆问题。以系统管理员的身份安装了SQLSERVER，则WindowsNT系统管理员则可以集成Windows身份认证的方式登陆 SQLSERVER 。
但其他用户的身份登陆WindowsNT系统就未必能够登陆SQLSERVER系统。若其他Windows用户想要登陆，则必须在 SQLSERVER中对其Windows用户进行访问Windows系统的授权。

2、SQLSERVER身份认证模式（刚开始默认为sa用户system administrator）
在WindowsNT,Windows2000/2003(除windows9x外)上运行SQLSERVER系统时，系统管理员设定的登陆模式可设为混合模式
即：既可以使用WindowsNT模式，又可以使用SQLSERVER账号登陆。

（二）有关于身份认证的具体的操作

一、针对WindowsNT认证模式登陆的账号

在授权后，可将其添加到固定服务器和数据库角色，使其具有操作服务器和数据库的权限（详情见一篇博文---固定服务器角色和固定数据库角色）。

1、可视化方式
1）先创建Windows2003系统用户，或创建域用户用户，在计算机管理中创建。
2）再将系统用户加入到SQLSERVER中。实例名---安全性---登陆,右键---新建用户---指明域名和账号

2、调用系统存储过程（前提也是要在系统中有这样的账号）
1）授权，我电脑的操作系统为Win2003SP2，计算机为域控制器，域名为TEST
    sp_grantlogin [@loginname=] 'login'
    @login 常量字符串
     login格式：域用户名  Teststeven
2）取消WindowsNT网络用户登陆权限
  sp_revokelogin [@loginname=] 'login'

3）
例1：条件：Test/user1和Test/user2在同一组下
        操作：若赋予WindowsNT普通用户user1登陆SQLSERVER的权限，但不赋予WindowsNT普通用户Test/user2登陆权限
        结果：Test/user1登陆系统，能够登陆访问数据库，但是Test/user2登陆系统，不能登陆访问数据库

例2：条件：如果Test/steven是Test/Admins组的成员
        操作1：用存储过程取消Test下的steven用户的权限，但是授权Test下Admins组相应权限
        结果1：可以通过steven登陆系统
        操作2：sp_denylogin [@loginname=] 'login'，授权Test下Admins组相应权限
        结果2：此时拒绝了test域下的steven ,即使Test域下的Admins组有权限，Steven也不能登陆

在安全性---登陆处---双击更改默认登陆数据库，此时没有默认密码。

4）在授权了WindowsNT普通用户后，用户可以登陆到SQLSERVER，但是无法访问数据库;
     此时在需要的登陆的数据库对象(如XSCJ)中创建用户的账户，即创建当前用户在数据库对象中的映射。
     sp_GrantDbAccess在每个用户数据库中创建用户账户。
例：Use XSCJ
       Exec sp_GrantDbAccess 'Test/Administrator'

二、混合认证模式下SQL Server登陆账号
1、可视化方式创建SQLSERVER登陆账号
  1）成功登陆WindowsNT
  2）实例名---右键属性---安全性---选择身份验证为"SQLSERVER和Windows"，只有这样才能更改登陆方式，.NET应用程序才能够通过SQLSERVER的相应角色的用户名访问数据库。
  3）通过企业管理器创建账号：安全性---[登陆，右键]---新建登陆---输入账号和密码---选择SQLSERVER身份验证

2、调用系统存储过程添加和删除SQLSERVER登陆账号
1）添加账号
sp_addlogin [@login=] 'login'
[
  [@password=] 'password'
  [@defaultdb=] 'defaultdb'
  [@defaultLanguage=] 'defaultlanguage'
  [@sid=] 'sid'
  [@encryptopt] 'null/'skip_encryption'/'skip_encryption_old '是否对密码加密'
]
1、不能从用户定义的事物中执行sp_addlogin
2、sp_password  更改用户密码 sp_password [@ps_old=]'oldpassword',[@ps_new=]'newpassword',[@loginname=]'login'
     sp_defaultdb  更改用户默认数据库 sp_defaultdb [@loginname=]'login',[@database=]'database'
3、只有sysadmin和securityadmin固定服务器角色才可以执行sp_addlogin

2）删除账号
sp_droplogin 'login  删除SQL登陆账号
1、不能删除任何数据库对象(如:XSCJ)现有用户的登陆账号。必须首先使用sp_dropuser删除该特定数据库用户；
     这些用户是指：在实例名---数据库，选择相应数据库---登陆---用户，右键---新建数据库用户，
     通过这个操作将已有用户映射到当前数据库上，删除时也要用sp_dropuser删除该特定数据库用户，取消映射
2、不能删除系统管理员sa的登陆账号
3、不能在用户定义的事务内执行sp_droplogin
4、只有sysadmin和securityadmin固定服务器角色的成员才能执行sp_droplogin

3）同样，通过sp_grantdbaccess将用户账户映射到相应的对象数据库中，使SQLSERVER用户拥有访问数据库的权限。

（三）举例

///授权WindowsNT账户登陆SQLSERVER的请求
Use master
///TEST为域名，steven为该域名下的用户
Exec sp_grantlogin [TESTsteven]
Go
///取消WindowsNT用户或组登陆SQLSERVER的账号
Use master
Exec sp_revokelogin [TESTsteven]
Go

///混合模式添加SQLSERVER系统的登陆账号
Use master
Exec sp_addlogin 'zhang'
Exec sp_password '7','zhang'  ///旧密码为空，则不写，否则在新密码'7'之前要加上旧密码
Exec sp_defaultdb 'zhang','XSCJ' ///设置默认的登陆数据库
Go

Go
Use master
Exec sp_droplogin 'zhang'
Go
Use master
Exec sp_addlogin 'wang','7','XSCJ','us_english'
Go

Use master
Exec sp_dropuser 'wang'  ///错误，当前数据库不存在用户wang,意思是这个登陆账号没有映射成为专用的数据库用户
Go
Exec sp_revokeLogin 'wang'  ///错误，不是系统帐户，WindowsNT的系统账号添加到SQLSERVER2000的登陆账号才能这样删除
Go
Use master
Exec sp_droplogin 'wang'  ///正确，可以删除SQLSERVER的系统账户
Go

Use master
Exec sp_addlogin 'stevenz','7','Test2','us_english'
Go
///可视化方式：此时通过"在实例名---数据库，选择相应数据库---登陆---用户---新建数据库用户"操作,将刚刚创建的stevenz映射到Test2数据库上，成为数据库用户
Use Test2
Exec sp_grantdbaccess 'stevenz'  ///代码创建账户在欲访问的数据库对象中的映射用户账户
Go
Use master
Exec sp_droplogin 'stevenz'  ///错误，会提示“登录 'stevenz' 在一个或多个数据库中有别名或映射到了用户上。请除去这些用户或别名后再除去该登录。”
Go
///此时要到特定的数据库用sp_dropuser去除映射，删除该用户
Use Test2
Exec sp_dropuser 'stevenz'  ///删除该数据库用户stevenz
Go
Exec sp_droplogin 'stevenz'  ///删除登陆账号stevenz
Go

///Windows集城身份验证
Use master
Exec sp_addlogin [TestAdministrator]  ///错误，不能这样授权域帐户或系统帐户，要用GrangLogin
Go
Use master
Exec sp_grantlogin [TESTAdministrator]  ///正确，WindowsNT系统账户和域账户只能用GrantLogin来授权,以达到Windows身份验证的目的
Go
Use XSCJ
Exec sp_grantdbaccess [TESTAdministrator]  ///在相应的数据库下，授权访问数据库的权限，删除时需要到响应数据库下用sp_dropuser去删除
Go
Use master
Exec sp_revokelogin [TESTAdministrator]
Go

                        参考书籍：

SQLSERVER2000实用教程(第二版)电子工业出版社 作者：郑阿奇

liuqi67

多谢管理员分享！