SQL Server:孤立用户详解

时间:2022-01-12 14:49:46

SQL Server 的用户安全管理分两层,整个SQL Server 服务器一层,每个数据库一层。

  • 在服务器层的帐号,叫登录账户(SQL Server:服务器角色),可以设置它管理整个SQL Server服务器(开启跟踪,修改 Sql Server 安全配置,备份所有数据库等)。
  • 在数据库一层,叫数据库账户(SQL Server:数据库角色),可以设置它对这个特定的数据库有读写、修改表结构、存储过程定义等权限。

登录帐号对于服务器而言的,数据库用户是针对特定数据库来讲的。就相当于一个房间里放着很多保险柜,你有房门钥匙了,必须得有每个保险柜的钥匙才能从保险柜里取东西。登录帐户是房门钥匙,数据库用户是保险柜钥匙。

1. 什么是孤立用户

如上述所述“登陆账户”和“数据库账户”之间存在一层映射,如果数据库“A”中有账户“cms”,但没有“登陆账户”与其对应,那么“cms”账户就无法连接SQL Server服务,因此它就没有任何作用,便成立一个孤立的用户。

2. 什么情况下会产生孤立用户

第一个服务器的情况:

  • 数据库A
  • 数据库A中有cms账户,并且也存在cms登陆账户

在第一个服务器中备份数据A,并在第二个服务器中还原该数据库,那么第二个服务器的情况:

  • 数据库A
  • 数据库A中有cms账户,不存在与其对应的登陆账户

此时在第二个服务器的数据库A中便产生了孤立账户cms,在第二个服务器中运行下述语句也可看到孤立账户cms:

Use A
exec sp_change_users_login 'report'

如果直接建立登陆账户“cms”,并制定其“数据库访问”为数据库A,会提示“用户**已存在”,如下图:

SQL Server:孤立用户详解

3. 如何解决孤立用户

解决孤立账户实质上就是建立一个“登陆账户”,并关联“登陆账户”和“数据库账户”。

建立一个登陆账户(一般与孤立账户同名,即“cms”),先不设置该“登陆账户”的数据库访问和数据角色,并设置该“登陆账户”的密码(一般也与孤立账户相同),然后连接“登陆账户”和“数据库账户”。

Use A
exec sp_change_users_login 'update_one', 'cms', 'cms'

这样在执行“exec sp_change_users_login 'report'”就不会看到孤立账户“cms”了。

sp_change_users_login的语法

exec sp_change_users_login 'update_one', '登陆账户名', '数据库账户名'

注意:“登陆账户”的账户名和密码都可以和“孤立账户”不同,应用程序的数据库配置应当为“登陆账户”的账户名和密码,而不是“数据库账户”。

4. 另一种解决办法

假设数据A中存在孤立账户cms,其密码为“123456”,应用程序的数据库配置也是此。为了能够使得应用程序能够正常使用,可以做如下操作:

  • 建立登陆账户“cms”,并设置密码和默认数据库分别为123456、cms
  • 设置该登陆账户的“服务器角色”为“System Administrators”
  • 不为该登陆账户设置相关“数据库访问”属性
  • 保存时会提示如下错误,不必理会
        SQL Server:孤立用户详解

这种方法仅仅是建立了一个类似于sa的cms用户,虽能够保障应用程序正常使用,但并不能解决孤立用户。