Différences

Ci-dessous, les différences entre deux révisions de la page.

--- sql_server:database:orphans [05/11/2025 10:22] – mdl
+++ sql_server:database:orphans [05/11/2025 10:30] (Version actuelle) – mdl
@@ Ligne 33: / Ligne 33: @@
   * Identifier les Orphelins
 <code TSQL>
+ -- Use your database
+USE [YourDatabaseName];
+GO
+-- Create a temporary table to store user information
+CREATE TABLE #DBUsers
+(
+    DBUserName NVARCHAR(128),
+    DBUserSID VARBINARY(85)
+);
+-- Insert database user information into the temp table
+INSERT INTO #DBUsers (DBUserName, DBUserSID)
+SELECT name, sid
+FROM sys.database_principals
+WHERE type_desc IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP');
+-- Select and compare with instance logins
+SELECT
+    u.DBUserName AS DatabaseUser,
+    u.DBUserSID AS DatabaseUserSID,
+    p.name AS InstanceLogin,
+    p.sid AS InstanceLoginSID,
+    CASE
+        WHEN p.sid IS NULL THEN 'Orphaned User'
+        ELSE 'Matched'
+    END AS Status
+FROM
+    #DBUsers u
+LEFT JOIN
+    sys.server_principals p ON u.DBUserSID = p.sid;
+-- Drop the temporary table
+DROP TABLE #DBUsers;
 </code>
@@ Ligne 43: / Ligne 76: @@
   * ou créer le Login en forçant le SID du user de BDD
 <code TSQL>
-create login 'robert'
+-- Lister le SID du user de BDD1211
+use [DB1]
+GO
+select name,type_desc,create_date,sid from sys.database_principals
+-- SQL LOGIN
+create login [robert]
 with password 'xxxxxx', sid='xxxxx'
+-- AD USER/GROUP
+create login [AD\robert] from windows
+with  sid='xxxxx'
 </code>