Comment obtenir la liste de tous les utilisateurs de la base de données

Question

Je vais obtenir la liste de tous les utilisateurs, y compris les utilisateurs Windows et 'sa', qui ont accès à une base de données particulière dans MS SQL Server. En fait, j'aimerais que la liste ressemble à ce qui est indiqué dans l'image suivante SQL Server Management Studio (c'est-à-dire la liste qui s'affiche lorsque vous développez la rubrique [databse] -> Security -> Users ) à une exception près : Je ne veux pas voir le 'dbo' dans la liste. J'aimerais plutôt voir l'utilisateur qui possède la base de données. Ainsi, par exemple, si 'sa' est l'utilisateur de la base de données. 'dbo' , 'sa' doit être inclus dans la liste au lieu de 'dbo' . Une autre note à ne pas manquer est la liste figurant dans l'annexe. SQL Server Management Studio affiche normalement les utilisateurs Windows en plus des utilisateurs SQL, et j'aimerais que ces utilisateurs soient également inclus.

Jusqu'à présent, j'ai pu formuler la requête suivante :

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

Cette requête est presque correcte, mais le problème est qu'elle ne satisfait pas à l'exigence de 'dbo' l'état.

Comment puis-je modifier cette requête ou dois-je en utiliser une autre ?

Answer 1

Pour le propriétaire du serveur SQL, vous devriez être en mesure d'utiliser :

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

Pour obtenir une liste des utilisateurs SQL :

select * from master.sys.server_principals

Réf. Astuce SQL Server : Comment trouver le propriétaire d'une base de données via T-SQL

Comment tester l'existence d'un utilisateur dans SQL Server ?

Answer 2

EXEC sp_helpuser

o

SELECT * FROM sysusers

Ces deux méthodes sélectionnent tous les utilisateurs de la base de données actuelle (et non du serveur).

Answer 3

Lorsque vous "voyez" quelque chose dans l'interface graphique (SSMS) et que vous vous dites "c'est ce dont j'ai besoin", vous pouvez toujours lancer Sql Profiler pour rechercher la requête qui a été utilisée.

Exécuter Sql Profiler. Attachez-le bien sûr à votre base de données.

Cliquez ensuite avec le bouton droit de la souris dans l'interface graphique (dans SSMS) et cliquez sur "Rafraîchir".
Ensuite, allez voir ce que Profiler "attrape".

J'ai obtenu le résultat suivant lorsque j'étais dans MyDatabase / Security / Users et que j'ai cliqué sur "refresh" sur "Users".

Encore une fois, ce n'est pas moi qui ai créé la clause WHERE et le LEFT OUTER JOIN, cela faisait partie de la requête SSMS. Et cette requête a été écrite par quelqu'un de chez Microsoft (vous savez, les gens qui connaissent le produit sur le bout des doigts, c'est-à-dire les experts), donc ils connaissent tous les "drapeaux" bizarres de la base de données.

Mais l'astuce SSMS/GUI -> Sql Profiler fonctionne dans de nombreux scénarios.

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

Answer 4

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

Cela permet de sélectionner tous les utilisateurs du serveur SQL que l'administrateur a créés !

Answer 5

Allez-y pour cela :

 SELECT name,type_desc FROM sys.sql_logins