March 24, 2004 at 12:48 pm
Does anyone have any script that would show users of all databases on a server. any help will be greatly appreciated.
TIA
March 24, 2004 at 1:03 pm
Cna give you the exact path, but I am sure that there is a script posted here.
March 24, 2004 at 1:14 pm
lots of different ways of doing this. Heres a sample:
EXEC sp_MSforeachdb "USE ? IF db_name() NOT IN ('tempdb', 'model', 'master', 'msdb')
SELECT db_name(), name FROM dbo.sysusers WHERE islogin = 1 AND sid > 0x01 "
Francis
March 24, 2004 at 2:16 pm
Thanks, but how do i use different databases in " use" statement.
March 24, 2004 at 2:33 pm
USE ?
The ? is a placeholder for different databases so USE ? switches from one to another.
If you want to make use of the database name within some code here is another way of doing the same thing:
DECLARE @sql nvarchar(1200)
DECLARE @db sysname
DECLARE @name char(11)
SET @db = ''
WHILE @db IS NOT NULL
BEGIN
SELECT @db = MIN( name ) FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'msdb', 'model')
AND name > @db
IF @db IS NOT NULL
BEGIN
SET @sql=N'SELECT name FROM '
+ @db + N'.dbo.sysusers s WHERE islogin = 1 AND sid > 0x01 '
--print @sql
EXEC sp_executesql @sql
END
END
Francis
March 24, 2004 at 2:40 pm
Thanks a lot. That worked..
March 25, 2004 at 8:02 am
You can do the same thing using the INFORMATION_SCHEMA VIEW. The syntax would be:
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
Supposedly the system tables could change in a future release of SQL Server, but the INFORMATION_SCHEMA VIEWs should stay the same.
March 25, 2004 at 8:38 am
You're right Andy. It probably would be better to replace
SELECT @db = MIN( name ) FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'msdb', 'model')
with
SELECT @db = MIN(CATALOG_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME NOT IN ('master', 'tempdb','msdb','model')
I have a couple of scripts where I use this and I think I will change them to your suggestion just to be safer. Too bad there isn't a view for the sysusers table.
Francis
March 25, 2004 at 11:08 am
If you change the set statement from:
SET @sql=N'SELECT name FROM '
to:
SET @sql=N'SELECT name as ' + @db + ' FROM '
it makes it easier to distingush the different databases.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply