[原創]否允許串連到資料庫引擎”和“登入”的區別

“是否允許串連到資料庫引擎”和“登入”的區別

sql server 2005中登入名稱的狀態裡面有個設定選項，裡麵包括了“是否允許串連到資料庫引擎”選項為“授予”、“拒絕”，還有一個“登入”選項，包括“啟用”，“禁用”。這兩項有什麼區別嗎？

 

下面分享一些個人的瞭解。

 

 

 

 

使用者要訪問訪問資料庫時，首先必須具備聯結到資料庫引擎的許可權，下一步DBMS會根據使用者提供的賬戶資訊進行核實，如果此使用者被啟用，則成功串連到相應資料庫。

 

分別在“是否允許串連到資料庫引擎”“登入”選項中選擇：“拒絕”，“啟用”以及“授予”，“禁用”兩組設定值。

串連資料庫得到以下提示：

 

 

 

 

 

 如果在“是否允許串連到資料庫引擎”中選擇“拒絕”，相當於執行了語句：  DENY CONNECT SQL TO [user_xxgl]<br />

此時不管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