如何获取所有数据库用户的列表

我将得到所有用户的列表，包括 Windows 用户和‘ sa’，他们可以访问 MS SQL Server 中的特定数据库。 基本上，我希望这个列表看起来像 SQL Server Management Studio中显示的那样(即当你展开 [databse] -> Security -> Users时所显示的列表) ，但有一个重要的例外: 我不希望在列表中看到 'dbo'。相反，我希望看到拥有数据库的实际用户。因此，例如，如果‘ sa’是 'dbo'，那么必须在列表中包含 'sa'而不是 'dbo'。另一个不容忽视的注意事项是，除了 SQL 用户，SQL Server Management Studio中的列表通常还显示 Windows 用户，我希望这些用户也包括在内。

到目前为止，我已经能够提出以下问题:

SELECT * FROM sys.database_principals where (type='S' or type = 'U')

这个查询几乎是正确的，但问题是它不满足 'dbo'条件。

如何更改此查询或使用其他查询？

569537 次浏览
小开

For the SQL Server Owner, you should be able to use:

select suser_sname(owner_sid) as 'Owner', state_desc, *
from sys.databases

For a list of SQL Users:

select * from master.sys.server_principals

Ref. SQL Server Tip: How to find the owner of a database through T-SQL

How do you test for the existence of a user in SQL Server?

小开 
SELECT name FROM sys.database_principals WHERE
type_desc = 'SQL_USER' AND default_schema_name = 'dbo'

This selects all the users in the SQL server that the administrator created!

小开 
EXEC sp_helpuser

or

SELECT * FROM sysusers

Both of these select all the users of the current database (not the server).

小开

I try to avoid using the "SELECT * " option and just pull what data I want or need. The code below is what I use, you may cull out or add columns and aliases per your needs.

I also us "IIF" (instant if) to replace binary 0 or 1 with a yes or no. It just makes it easier to read for the non-techie that may want this info.

Here is what I use:

SELECT
name AS 'User'
, PRINCIPAL_ID
, type AS 'User Type'
, type_desc AS 'Login Type'
, CAST(create_date AS DATE) AS 'Date Created'
, default_database_name AS 'Database Name'
, IIF(is_fixed_role LIKE 0, 'No', 'Yes') AS 'Is Active'
FROM master.sys.server_principals
WHERE type LIKE 's' OR type LIKE 'u'
ORDER BY [User], [Database Name];
GO

Hope this helps.

小开
最佳答案

Whenever you 'see' something in the GUI (SSMS) and you're like "that's what I need", you can always run Sql Profiler to fish for the query that was used.

Run Sql Profiler. Attach it to your database of course.

Then right click in the GUI (in SSMS) and click "Refresh".
And then go see what Profiler "catches".

I got the below when I was in MyDatabase / Security / Users and clicked "refresh" on the "Users".

Again, I didn't come up with the WHERE clause and the LEFT OUTER JOIN, it was a part of the SSMS query. And this query is something that somebody at Microsoft has written (you know, the peeps who know the product inside and out, aka, the experts), so they are familiar with all the weird "flags" in the database.

But the SSMS/GUI -> Sql Profiler tricks works in many scenarios.

SELECT
u.name AS [Name],
'Server[@Name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/User[@Name=' + quotename(u.name,'''') + ']' AS [Urn],
u.create_date AS [CreateDate],
u.principal_id AS [ID],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = 'CO'
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K' ,'E', 'X'))
ORDER BY
[Name] ASC
小开

Go for this:

 SELECT name,type_desc FROM sys.sql_logins
小开

To run a query returning users of individual databases, try this:

EXEC sp_MSforeachdb 'USE ? <QUERY HERE>'

This will run a query (and return a result) for each database. So to get all users (probably with a lot of internal users and roles you are not interested in, try:

EXEC sp_MSforeachdb 'USE ?; SELECT DB_NAME(), * FROM sys.database_principals;'

Just apply filters mentioned in other replys to get exactly the subset you are looking for.


提交答案