Je suppose que la procédure devrait être quelque chose comme ceci: mais le problème est que EXED ("Utiliser" + @DB) ne fonctionne pas. Et j'ai toujours une liste d'utilisateurs de la base de données actuellement choisie. Comment devrais-je résoudre ce problème? P.s.: Je souhaite que ce code fonctionne sur les serveurs SQL 2000 et 2005.
3 Réponses :
Vous pouvez également utiliser le code non documenté mais bien utilisé sp_msforeachdb code> stocké stock - voir ici pour plus de détails ou voir une autre Blog POST ICI : exec sp_MSforeachdb 'select * from ?.sys.sysusers'
+1 génial! J'essayais de comprendre un moyen de faire fonctionner du code Stee1rat d'une manière ou d'une autre, l'apprentissage entre-temps, puis vous proposez cette magnifique procédure stockée SP_MSForeachdb! Merci!
C'est juste génial! Merci beaucoup! Maintenant, je peux désormais laisser tomber l'utilisateur dans toutes les bases de données en utilisant une simple commande SP_MSForeachdb 'Utilisation ?; EXEC SP_Dropuser '' Testuser123 '' ':) Je me demande simplement pourquoi cette procédure est-elle non documentée? C'est tellement utile :)
Voici une belle requête de http://www.sqlservercratral.com/scripts/ Administration / 63841 / Si vous n'avez pas de compte, il s'agit d'une inscription gratuite et d'une très belle ressource.
met tout dans une table Temp, alors vous pouvez faire ce que vous voulez avec elle. P >
USE MASTER
GO
BEGIN
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8'
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser
(
ServerName varchar(256),
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85)
)
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'
ELSE IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
JOIN [?].sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
SELECT *
FROM #TUser
ORDER BY DBName, [name], GroupName
DROP TABLE #TUser
END
Cela fonctionne toujours dans SQL Server 2012 après avoir remplacé la ligne sinon si gauche (couler (ServerProperty («productversion») en tant que varchar (1)), 1) = '9' avec (Cast (ServerProperty («productVersion») comme Varchar (2)), 2) = '11' . (Bien sûr, le chèque peut être rendu plus général).
Voici comment créer une liste de tous les utilisateurs de toutes les bases de données dans une instance SQL Server. Je pense que c'est ce que vous cherchez.
Si vous souhaitez une liste de tous les utilisateurs de toutes les bases de données pour toutes les instances, vous devriez pouvoir modifier le script pour le faire. Il suffit de développer les techniques utilisées dans le script. p>
SET NOCOUNT ON;
DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sql AS VARCHAR(MAX);
DECLARE @ServerName AS SYSNAME;
SET @ServerName = (
SELECT @@SERVERNAME
);
IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
DROP TABLE #User
END;
CREATE TABLE #User (
ServerName SYSNAME
,DbName SYSNAME
,UserName SYSNAME NULL
,LoginType VARCHAR(255) NULL
,Permission VARCHAR(255) NULL
,StateOf VARCHAR(255) NULL
,AccessLevel VARCHAR(255) NULL
,ObjectName SYSNAME NULL
);
SET @DbName = '';
INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;
WHILE @DbName IS NOT NULL
BEGIN
SET @DbName = (
SELECT MIN(DbName)
FROM @Database
WHERE DbName > @DbName
)
/*
PUT CODE HERE
*/
SET @sql = '
INSERT INTO #User (
ServerName
,DbName
,UserName
,LoginType
,Permission
,StateOf
,AccessLevel
,ObjectName
)
SELECT ''' + @ServerName + ''' AS ServerName
,''' + @DbName + ''' AS DbName
,princ.name AS UserName
,princ.type_desc AS LoginType
,perm.permission_name AS Permission
,perm.state_desc AS StateOf
,perm.class_desc AS AccessLevel
,object_name(perm.major_id) AS ObjectName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals princ
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
'
EXEC (@sql)
END;
SELECT *
FROM #User;
DROP TABLE #User;