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

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

Windows 사용자와 'sa'를 포함하여 MS SQL Server의 특정 데이터베이스에 액세스할 수 있는 모든 사용자의 목록을 가져올 것입니다.기본적으로, 나는 목록이 다음과 같이 표시되기를 원합니다.SQL Server Management Studio(즉, 확장할 때 표시되는 목록)[databse] -> Security -> Users) 한 가지 중요한 예외를 제외하고는:저는 그것을 보고 싶지 않습니다.'dbo'명부에오히려 데이터베이스를 소유한 실제 사용자를 보고 싶습니다.예를 들어, 'sa'가 다음과 같은 경우'dbo','sa'목록에 포함해야 합니다.'dbo'놓치지 말아야 할 또 다른 참고 사항은 목록입니다.SQL Server Management Studio일반적으로 SQL 사용자 외에 Windows 사용자도 표시됩니다. 이러한 사용자도 포함했으면 합니다.

지금까지 다음과 같은 질문을 할 수 있었습니다.

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

이 쿼리는 거의 옳지만 문제는 이 쿼리가 다음을 만족시키지 못한다는 것입니다.'dbo'조건.

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

SQL Server Owner의 경우 다음을 사용할 수 있어야 합니다.

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

SQL 사용자 목록의 경우:

select * from master.sys.server_principals

참고. SQL Server 팁: T-SQL을 통해 데이터베이스 소유자를 찾는 방법

SQL Server에 사용자가 있는지 어떻게 테스트합니까?

EXEC sp_helpuser

또는

SELECT * FROM sysusers

둘 다 서버가 아닌 현재 데이터베이스의 모든 사용자를 선택합니다.

GUI(SSMS)에서 무언가를 '확인'하고 "그것이 내가 필요로 하는 것"과 같은 느낌이 들 때마다 SQL Profiler를 실행하여 사용된 쿼리를 검색할 수 있습니다.

SQL 프로파일러를 실행합니다.물론 데이터베이스에 첨부합니다.

그런 다음 (SSMS에서) GUI를 마우스 오른쪽 단추로 클릭하고 "새로 고침"을 클릭합니다.
그런 다음 프로파일러가 무엇을 "획득"하는지 확인합니다.

내 데이터베이스 / 보안 / 사용자에 있을 때 "사용자"에서 "새로 고침"을 클릭하면 다음과 같은 메시지가 나타납니다.

다시 말하지만, 저는 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 FROM sys.database_principals WHERE
type_desc = 'SQL_USER' AND default_schema_name = 'dbo'

이렇게 하면 관리자가 생성한 SQL 서버의 모든 사용자가 선택됩니다!

실행:

 SELECT name,type_desc FROM sys.sql_logins

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

또한 이진 0 또는 1을 yes 또는 no로 바꾸려면 "IIF"(instant if)를 사용합니다.이것은 이 정보를 원하는 기술자가 아닌 사람들을 위해 더 쉽게 읽을 수 있게 해줍니다.

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

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

이게 도움이 되길 바랍니다.

개별 데이터베이스의 사용자를 반환하는 쿼리를 실행하려면 다음을 시도하십시오.

EXEC sp_MSforeachdb 'USE ? <QUERY HERE>'

그러면 각 데이터베이스에 대한 쿼리가 실행되고 결과가 반환됩니다.모든 사용자(아마도 관심 없는 내부 사용자와 역할이 많은 사용자)를 확보하려면 다음을 시도하십시오.

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

원하는 하위 집합을 정확히 얻으려면 다른 응답에 언급된 필터를 적용하십시오.

다음 배치를 사용하여 소유자를 포함한 모든 데이터베이스의 모든 로그인을 나열합니다.

테이블에 합니다.##DBLogin 소유자 합니다. if ), 소자유정추소의로유그인업형다데니합트이을자.NULL합니다.SELECT임시 테이블에서 테이블을 다시 삭제하면 각 DB에 대한 모든 로그인을 한 번에 실행할 수 있는 멋진 테이블 결과를 얻을 수 있습니다.

USE tempdb

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[##DBLogin]') AND type in (N'U'))
DROP TABLE [dbo].[##DBLogin]

SET NOCOUNT ON;
SELECT DB_NAME() AS [DBLogin_Database], name AS DBLogin_Name, type_desc AS DBLogin_Type, 0 AS DBLogin_Owner
INTO ##DBLogin
FROM sys.database_principals WHERE 1 = 2;

DECLARE @Query AS NVARCHAR(MAX) = 'USE ? INSERT INTO ##DBLogin SELECT ''?'' AS [DBLogin_Database], name AS DBLogin_Name, type_desc AS DBLogin_Type, 0 AS DBLogin_Owner FROM sys.database_principals WHERE type != ''R'';'
EXEC sp_MSforeachdb @Query;

INSERT INTO ##DBLogin 
SELECT DB.name AS DBLogin_Database, SUSER_SNAME(DB.owner_sid) AS DBLoginName, type_desc AS DBLogin_Type, 1 AS DBLogin_Owner
FROM sys.databases DB
LEFT OUTER JOIN sys.server_principals SP ON SUSER_SNAME(DB.owner_sid) = SP.name

-- Do some guess-work for the owner's login type (comment out if you prefere to have NULL instead of guess-work)
UPDATE A SET A.DBLogin_Type = (SELECT TOP(1) B.DBLogin_Type FROM ##DBLogin B WHERE B.DBLogin_Name = A.DBLogin_Name AND B.DBLogin_Type IS NOT NULL) FROM ##DBLogin A WHERE (A.DBLogin_Type IS NULL) AND (A.DBLogin_Owner = 1);
UPDATE ##DBLogin SET DBLogin_Type = 'WINDOWS_USER' WHERE (DBLogin_Type IS NULL) AND (DBLogin_Owner = 1) AND (DBLogin_Name LIKE '%_\_%');
UPDATE ##DBLogin SET DBLogin_Type = 'SQL_USER' WHERE (DBLogin_Type IS NULL) AND (DBLogin_Owner = 1) AND (DBLogin_Name NOT LIKE '%_\_%');

SELECT DBLogin_Database, DBLogin_Name, MAX(DBLogin_Type) AS DBLogin_Type, CAST(MAX(DBLogin_Owner) AS BIT) AS DBLogin_Owner FROM ##DBLogin 
GROUP BY DBLogin_Database, DBLogin_Name
ORDER BY DBLogin_Database, DBLogin_Owner DESC, DBLogin_Name, DBLogin_Type

DROP TABLE [dbo].[##DBLogin]

例주: 것이며, 아직 수 있습니다.소유자 로그인 유형은 여기 저기에서 약간 가져온 것이며 아직 알 수 없는 경우 추측할 수 있습니다. 달기(이된 줄에 따라 달기(포함된라인달기주석라따에운러웃의심스아경우라인포▁comment▁that라)인▁in▁according▁lines)UPDATE진술).

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