sqlserver 维护"孤立"用户

--查看孤立用户，并建立信任连接，常在移动并附加数据库后出现。

USE <database_name>;
GO; 
EXEC sp_change_users_login @Action='Report'; --1、检测孤立用户.
GO;
EXEC sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'; --2、重新建立信任连接.
GO;
USE master 
GO;
EXEC sp_password @old=NULL, @new='password', @loginame='<login_name>'; --3、更改登陆密码.
GO;

--===============================================================

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_ShowOrphanUsers]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_ShowOrphanUsers]
GO

CREATE PROC dbo.usp_ShowOrphanUsers
AS
--
-- usp_ShowOrphanUsers.sql - 检查并列出所有孤立用户
--
-- 2007-06-19 Pedro Lopes (Novabase)
-- EXEC dba_database..usp_ShowOrphanUsers
--
BEGIN
 CREATE TABLE #Results
 ([Database Name] sysname COLLATE Latin1_General_CI_AS, 
 [Orphaned User] sysname COLLATE Latin1_General_CI_AS,
 [Type User] sysname COLLATE Latin1_General_CI_AS)

 SET NOCOUNT ON

 DECLARE @DBName sysname, @Qry NVARCHAR(4000)

 SET @Qry = ''
 SET @DBName = ''

 WHILE @DBName IS NOT NULL
 BEGIN
  SET @DBName = 
  (SELECT MIN(name) 
  FROM master..sysdatabases 
   WHERE name NOT IN 
   ('master', 'model', 'tempdb', 'msdb', 'distribution', 'pubs', 'northwind', 'dba_database')
   AND DATABASEPROPERTY(name, 'IsOffline') = 0 
      AND DATABASEPROPERTY(name, 'IsSuspect') = 0 
      AND name > @DBName
)
 
  IF @DBName IS NULL BREAK

  SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name], 
    CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
    [Type User] = 
     CASE isntuser 
      WHEN ''0'' THEN ''SQL User''
      WHEN ''1'' THEN ''NT User''
      ELSE ''Not Available''
     END
    FROM ' + QUOTENAME(@DBName) + '..sysusers su
    WHERE su.islogin = 1
    AND su.name NOT IN (''INFORMATION_SCHEMA'', ''sys'', ''guest'', ''dbo'', ''system_function_schema'')
    AND NOT EXISTS (SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid)'
  INSERT INTO #Results 
  EXEC master..sp_executesql @Qry
 END
 SELECT * 
 FROM #Results 
 ORDER BY [Database Name], [Orphaned User]
 IF @@ROWCOUNT = 0
  PRINT 'No orphaned users exist in this server.'
END
GO

--列表登录用户
CREATE PROC usp_sqlLogin_audit
AS
/*
Audit SQL Server Users
--------------------------------------------------------------------------
*/

SET NOCOUNT ON

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
 DROP TABLE ##Users;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
 DROP TABLE ##DBUsers;

-- ***************************************************************************
-- Always run this from master  --Not needed
-- USE master 
-- ***************************************************************************

-- ***************************************************************************
-- Declare local variables
DECLARE @DBName VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
-- ***************************************************************************

-- ***************************************************************************
-- Get the SQL Server logins
-- Create Temp User table
CREATE TABLE ##Users (
[sid] VARBINARY(500) NULL,
[Login Name] varchar(200) NULL,
[Default Database] varchar(50) NULL,
[Login Type] varchar(18),
[AD Login Type] varchar(16),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3));
---------------------------------------------------------
INSERT INTO ##Users SELECT sid,
 loginname AS [Login Name], 
 dbname AS [Default Database],
 CASE isntname 
 WHEN 1 THEN 'AD Login'
 ELSE 'SQL Login'
 END AS [Login Type],
 CASE 
 WHEN isntgroup = 1 THEN 'AD Group'
 WHEN isntuser = 1 THEN 'AD User'
 ELSE ''
 END AS [AD Login Type],
 CASE sysadmin
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [sysadmin],
 CASE [securityadmin]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [securityadmin],
 CASE [serveradmin]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [serveradmin],
 CASE [setupadmin]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [setupadmin],
 CASE [processadmin]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [processadmin],
 CASE [diskadmin]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [diskadmin],
 CASE [dbcreator]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [dbcreator],
 CASE [bulkadmin]
 WHEN 1 THEN 'Yes'
 ELSE 'No'
 END AS [bulkadmin]
FROM master.dbo.syslogins;
---------------------------------------------------------
SELECT [Login Name],
[Default Database],
[Login Type],
[AD Login Type],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin]
FROM ##Users
ORDER BY [Login Type], [AD Login Type], [Login Name]
-- ***************************************************************************
-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers (
[Database User ID] VARCHAR(250),
[Server Login] VARCHAR(500),
[Database Role] VARCHAR(24),
[Database] VARCHAR(24));
-- ***************************************************************************
-- ***************************************************************************
-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR 
 SELECT name
 FROM master..sysdatabases
 WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
-- ***************************************************************************
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT 
 FROM csrDB
 INTO @DBName
-- ***************************************************************************
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
 BEGIN
-- ***************************************************************************
-- ***************************************************************************
-- 
 SELECT @SQLCmd = 'INSERT ##DBUsers ' +
 ' SELECT su.[name] AS [Database User ID], ' +
 ' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
 ' COALESCE (sug.name, ''Public'') AS [Database Role],' + '''' + @DBName + ''' AS [Database]' +
 ' FROM [' + @DBName + '].[dbo].[sysusers] su' +
 ' LEFT OUTER JOIN ##Users u' +
 ' ON su.sid = u.sid' +
 ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
 ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' +
 ' ON sm.groupuid = sug.uid)' +
 ' ON su.uid = sm.memberuid ' +
 ' WHERE su.hasdbaccess = 1' +
 ' AND su.[name] != ''dbo'' '
 EXEC (@SQLCmd)
-- ***************************************************************************
-- ***************************************************************************
-- Get the next database name
 FETCH NEXT 
 FROM csrDB
 INTO @DBName
-- ***************************************************************************
-- ***************************************************************************
-- End of the cursor loop
 END
-- ***************************************************************************
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
-- ***************************************************************************
-- Return the Database User data
SELECT * 
 FROM ##DBUsers
ORDER BY [Database],[Database User ID];
-- ***************************************************************************
-- ***************************************************************************
-- Clean up - delete the Global temp tables
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
 DROP TABLE ##Users;

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
 DROP TABLE ##DBUsers;
-- ***************************************************************************
GO