[原创]否允许连接到数据库引擎”和“登录”的区别

“是否允许连接到数据库引擎”和“登录”的区别

sql server 2005中登录名的状态里面有个设置选项，里面包括了“是否允许连接到数据库引擎”选项为“授予”、“拒绝”，还有一个“登录”选项，包括“启用”，“禁用”。这两项有什么区别吗？

 

下面分享一些个人的了解。

 

 

 

 

用户要访问访问数据库时，首先必须具备联接到数据库引擎的权限，下一步DBMS会根据用户提供的账户信息进行核实，如果此用户被启用，则成功连接到相应数据库。

 

分别在“是否允许连接到数据库引擎”“登录”选项中选择：“拒绝”，“启用”以及“授予”，“禁用”两组设置值。

连接数据库得到以下提示：

 

 

 

 

 

 如果在“是否允许连接到数据库引擎”中选择“拒绝”，相当于执行了语句：  DENY CONNECT SQL TO [user_xxgl]

此时不管DBMS发现当前试图连接的用户是user_xxgl时，就直接把它踢掉了，不管其是否输入正确的密码信息。此选项其对sysadmin 无效。

 

 

 如果在“登录”中选择“禁用”，相当于执行了语句：  ALTER LOGIN [user_xxgl] DISABLE

此时不管DBMS发现当前试图连接的用户是user_xxgl时，并且用户输入了正确的密码信息后，才提示用户：此用户被停职了，密码正确也没用。此选项无法应用于windows平台上的用户及用户组。

 

 

关于这两个的不同微软的讨论区，有些讨论:

 

In order to successfully login, you'd need to both Grant permission to connect to the database engine and Enable the login.

Granting permission to the DB Engine basically says this login can connect to this server instance...enabling/disabling a login does just that - enables or disables it's ability to login to the instance.

If you still have trouble after this, also take a look at the default database for the login and make sure the login has permission to access that database.  For more information, see the following topics in BOL:

CREATE LOGIN (server level login to allow permission at the instance level)

CREATE USER (database level user that maps to a login to allow permissions at a particular database level)

Once you have the user in a database, you'll need to grant permissions to that user to allow it to do whatever you are trying to do with it (i.e. select, update, delete, etc.)

 

alter login disable will block all logins from connecting to sql server.

denying connect sql to the login will not block members of the sysadmin fixed server role from logging in because denys do not apply to sysadmins.

 

Disabled logins can be impersonated via execute as login = 'login_name' but they can't directly connect.

 

For a given databse, you can deny the connect permission to the user associated with the login and this will block access to the user.  If the login is a sysadmin then the database user will be dbo and you cannot block out the dbo.

 

Denied: sys.syslogins WHERE hasaccess = 0
Disabled: sys.server_principals WHERE is_disabled = 1
Locked: sys.syslogins: WHERE LOGINPROPERTY (name , 'IsLocked') = 1 (only for SQL logins)

 

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/f41feb6d-62ec-424f-8b25-7e6ef1da10e2

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/62f058c1-d0d6-4420-90e0-555794b889a4/

http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/72d68a6a-5be5-4a50-8d15-7e8a9515e889