모든 데이터베이스 사용자 목록을 가져 오는 방법

모든 데이터베이스 사용자 목록을 가져 오는 방법

---

MS SQL Server의 특정 데이터베이스에 액세스 할 수있는 Windows 사용자 및 'sa'를 포함한 모든 사용자 목록을 가져 오겠습니다. 기본적으로 한 가지 중요한 예외를 제외 SQL Server Management Studio하고 목록 이에 표시된 것과 같이 보이기를 원합니다 (예 : 확장 할 때 표시 되는 목록). 목록에서 [databse] -> Security -> Users를보고 싶지 않습니다 'dbo'. 오히려 데이터베이스를 소유 한 실제 사용자를보고 싶습니다. '사'는 인 경우에 따라서, 예를 들어 'dbo', 'sa'대신 목록에 포함되어야합니다 'dbo'. 놓쳐서는 안될 또 다른 참고 사항은 SQL Server Management Studio일반적으로 목록에 SQL 사용자 외에 Windows 사용자가 표시된다는 것입니다. 이러한 사용자도 포함 되었으면합니다.

지금까지 다음 쿼리를 만들 수있었습니다.

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

이 쿼리는 거의 맞지만 문제는 'dbo'조건을 충족하지 않는다는 것입니다.

이 쿼리를 변경하려면 어떻게해야합니까? 아니면 다른 쿼리를 사용해야합니까?

---

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에서 사용자의 존재 여부를 어떻게 테스트합니까?

---

EXEC sp_helpuser

또는

SELECT * FROM sysusers

이 두 가지 모두 현재 데이터베이스 (서버 아님)의 모든 사용자를 선택합니다.

---

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

그러면 관리자가 생성 한 SQL 서버의 모든 사용자가 선택됩니다!

---

GUI (SSMS)에서 무언가를 '볼'때 "그것이 필요합니다"라고 생각할 때마다 항상 Sql Profiler를 실행하여 사용 된 쿼리를 검색 할 수 있습니다.

SQL Profiler를 실행합니다. 물론 데이터베이스에 연결하십시오.

그런 다음 GUI (SSMS)를 마우스 오른쪽 버튼으로 클릭하고 "새로 고침"을 클릭합니다.
그런 다음 Profiler가 "잡는"항목을 확인하십시오.

MyDatabase / Security / Users에있을 때 "사용자"에서 "새로 고침"을 클릭했을 때 아래와 같은 메시지가 표시됩니다.

다시 말하지만, WHERE 절과 LEFT OUTER JOIN을 생각 해낸 것이 아니라 SSMS 쿼리의 일부였습니다. 그리고이 쿼리는 마이크로 소프트가 작성한 것이므로 데이터베이스의 모든 이상한 "플래그"에 익숙합니다.

그러나 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

---

이것을 위해 가십시오 :

 SELECT name,type_desc FROM sys.sql_logins

---

"SELECT *"옵션을 사용하지 않고 내가 원하거나 필요한 데이터를 가져 오려고합니다. 아래 코드는 내가 사용하는 코드이며 필요에 따라 열과 별칭을 컬 아웃하거나 추가 할 수 있습니다.

또한 바이너리 0 또는 1을 yes 또는 no로 대체하기 위해 "IIF"(즉시)를 사용합니다. 이 정보를 원할 수있는 비전문가도 쉽게 읽을 수 있습니다.

내가 사용하는 것은 다음과 같습니다.

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

도움이 되었기를 바랍니다.

참고 URL : https://stackoverflow.com/questions/18866881/how-to-get-the-list-of-all-database-users