Show a list of disabled users 1

[seaport]

As far as I know, in SQL Server 2005, I can use "REVOKE CONNECT FROM" and "GRANT CONNECT TO" to disable/enable a user.
However, how I do I get a list of users that are disabled/enabled?
I tried "sp_helpuser", but it does not provide the information.

Any suggestion?

Thanks in advance.

Seaport

 

[mrdenny]

This will give you a list of the disabled users.

select *
from sys.server_principals
where is_disabled = 1

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[seaport]

Denny,

Thanks for the reply. But I do not think we can talking about the same thing. Your code shows the list of logins of the server that are disabled. What I am trying to get is the list of database users that are disabled.

Here are some additional information. From the SQL management studio, I can disable/enabled a login at Login Properties - Status screen. However, this is no screen for enable/disable a database user. So, I cannot find out whether a database user is disabled or not through the Management Studio.

I ran into this problem when I assigned some permission to the user guest in a database. But I got an error -
The server principal "LoginName" is not able to access the database "DatabaseName" under the current security context.

Eventually, I found out that the user guest is disabled in that database.

Seaport

 

[mrdenny]

The guest user is disabled on all databases by default. It's recommended that you not enable the guest account or grant rights to it as these rights would then be granted to everyone who connects to the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[seaport]

Thanks, Denny,

I think that Disabling a user is rarely needed (except for guest user) because I can always disable a login or Grant/Remove a login's access to the SQL Server. That's why SQL serer 2005 does not provide a way to display disabled users.

Seaport