sql server - How can I know if the SA user is disabled? - Stack Overflow

admin管理员组

文章数量:1401149

I have been doing some research on the SA user account in SQL.

I placed the SELECT * FROM syslogins statement and in the hasaccess column the SA user appears with 1. However, when checking the Microsoft SQL Server screen, it appears to me with the X, which I interpret as not having access.

My question is, why does hasaccess=1 appear if SA user is disabled?

I have been doing some research on the SA user account in SQL.

I placed the SELECT * FROM syslogins statement and in the hasaccess column the SA user appears with 1. However, when checking the Microsoft SQL Server screen, it appears to me with the X, which I interpret as not having access.

My question is, why does hasaccess=1 appear if SA user is disabled?

• sql-server
• authentication
• settings
• sa

Share Improve this question Follow asked Mar 25 at 2:28 G07G07 122 bronze badges 3

• 5 syslogins is deprecated. You should be using sys.server_principals or sys.sql_logins and then the answer is obvious – Martin Smith Commented Mar 25 at 2:49
• 1 You should use (learn.microsoft/en-us/sql/relational-databases/… instead of the old syslogins. syslogins is from SQL Server 2000 and The Enable/Disable feature of a login is not available in SQL Server 2000. So hasaccess does not represent the state Enable/Disable of a login in newer version of SQL Server – Squirrel Commented Mar 25 at 2:50
• 1 some info on the SQL Server 2000 state of affairs stackoverflow/a/629891/73226 – Martin Smith Commented Mar 25 at 8:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

To expand what has been stated at in the comments, let's first look at sys.syslogins (Transact-SQL), which very clearly states:

Important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So, quite clearly, you should not be using sys.syslogins. The correct system view to use is either sys.server_principals or sys.sql_logins, per the linked documentation in the quote block above.

To find out if a LOGIN is disabled, you would check the value of is_disabled using either of those views. 0 denotes the login is enabled, and 1 disabled:

DECLARE @LoginName sysname = N'sa';

SELECT is_disabled
FROM sys.server_principals
WHERE name = @LoginName;

SELECT is_disabled
FROM sys.sql_logins
WHERE name = @LoginName;

本文标签： sql serverHow can I know if the SA user is disabledStack Overflow