Как получить список всех пользователей базы данных

Я собираюсь получить список всех пользователей, включая пользователей Windows и "sa", которые имеют доступ к определенной базе данных в MS SQL Server. В принципе, я хотел бы, чтобы список выглядел так, как показано в SQL Server Management Studio (т.е. Список, который отображается при расширении [databse] -> Security -> Users) одним важным исключением: я не хочу видеть 'dbo' в списке, Скорее, я хотел бы видеть фактического пользователя, которому принадлежит база данных. Так, например, если "sa" - это 'dbo', 'sa' должен быть включен в список вместо 'dbo'. Еще одно примечание, которое не должно быть пропущено, в списке SQL Server Management Studio обычно отображаются пользователи Windows в дополнение к пользователям SQL, и я хотел бы, чтобы эти пользователи также были включены.

До сих пор мне удалось найти следующий запрос:

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

Этот запрос почти прав, но проблема в том, что он не удовлетворяет условию 'dbo'.

Как я могу изменить этот запрос или использовать другой?

Ответы

Ответ 1

Для владельца SQL Server вы сможете использовать:

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

Список пользователей SQL:

select * from master.sys.server_principals

Ref. Совет SQL Server: как найти владельца базы данных через T-SQL

Как вы проверяете наличие пользователя на SQL Server?

Ответ 2

EXEC sp_helpuser

или

SELECT * FROM sysusers

Оба из них выбирают всех пользователей текущей базы данных (а не сервера).

Ответ 3

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

Это выбирает всех пользователей на сервере SQL, созданных администратором!

Ответ 4

Всякий раз, когда вы "видите" что-то в графическом интерфейсе (SSMS) и вам нравится "то, что мне нужно", вы всегда можете запустить Sql Profiler для поиска запроса, который был использован.

Запустите Sql Profiler. Присоедините его к вашей базе данных, конечно.

Затем щелкните правой кнопкой мыши в графическом интерфейсе (в SSMS) и нажмите "Обновить".
А потом иди посмотри, что Profiler "ловит".

Я получил ниже, когда я был в MyDatabase/Security/Users и нажал "обновить" на "Пользователи".

Опять же, я не придумал предложение WHERE и LEFT OUTER JOIN, это было частью запроса SSMS. И этот запрос - кое-что, что написал кто-то из Microsoft, поэтому они знакомы со всеми странными "флагами" в базе данных.

Но приемы SSMS/GUI → Sql Profiler работают во многих случаях.

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

Ответ 5

Пойти на это:

 SELECT name,type_desc FROM sys.sql_logins

Ответ 6

Я стараюсь избегать использования опции "SELECT *" и просто извлекаю те данные, которые мне нужны или нужны. Код ниже - то, что я использую, вы можете отключить или добавить столбцы и псевдонимы в соответствии с вашими потребностями.

Я также использую "IIF" (моментальный, если), чтобы заменить двоичные 0 или 1 на "да" или "нет". Это просто облегчает чтение для технарей, которые могут хотеть эту информацию.

Вот что я использую:

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

Надеюсь это поможет.