December 19, 2008 at 6:35 am
I am interested in getting a list of users and their assigned databases. What is the best way to do this? Is there a simple SQL query to do this?
Thanks,
December 19, 2008 at 7:11 am
A good place to start would be in the Scripts section of this site. Here is a list of security based scripts: http://www.sqlservercentral.com/Scripts/Administration/Security/. One of these should point you in the right direction anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 20, 2008 at 5:23 pm
Give this a try:
declare @sql varchar(8000)
Declare @DB_Objects varchar(8000)
Select @DB_Objects = ' name COLLATE DATABASE_DEFAULT as [Name]
From %D%.dbo.sysusers where uid not in (16384,16385,16386,16387,16389,16390,16391,16392,16393)and sid is not null
'
Select @sql = 'SELECT DBName,Name FROM
(Select '+Cast(dbid as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DB_objects, '%D%', [name])
From master.dbo.sysdatabases
Where [name] = 'master'
Select @sql = @sql + 'UNION ALL Select '+Cast(dbid as varchar(9))+', '''+[name]+''', '
+ Replace(@DB_objects, '%D%', [name])
From master.dbo.sysdatabases
Where [name] != 'master'
print @sql
EXEC (@sql)
MJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply