May 9, 2007 at 10:58 am
I want a query which returns sysusers information for all the databases in a Server.
| ServName | dbname | login | Public | db_owner | db_accessadmin | db_datareader | db_datawriter | db_ddladmin | db_securityadmin | db_backupoperator | db_denydatareader | db_denydatawriter | app_user |
| TestServer | [TestDB] | guest | N | N | N | N | N | N | N | N | N | N | N |
| TestServer | [TestDB] | sa | N | Y | N | N | N | N | N | N | N | N | N |
| TestServer | [TestDB] | User1 | N | N | N | Y | Y | N | N | N | N | N | Y |
| TestServer | [TestDB] | User2 | N | N | N | Y | N | N | N | N | N | N | Y |
| TestServer | [TestDB] | User3 | N | N | N | Y | N | N | N | N | N | N | N |
Thanks for your time.
--Prasad
May 9, 2007 at 11:06 am
Check out those tables, then all you'd need to do is pivot the data :
Select * from master.dbo.SysUsers
Select * from master.dbo.SysLogins
May 9, 2007 at 11:06 am
you could use a cursor to get all the DB names and cycle through them running your query out of the sysusers tables...
Ben Sullins
bensullins.com
Beer is my primary key...
May 9, 2007 at 11:41 am
Ben, Thanks for the reply. i can use cursor to loop thru all DB's.But all i want the info in the format specified above. usage of temp tables is permitted. any code snippet is highly appreciated.
May 9, 2007 at 11:48 am
Here's a query I've used:
--This script list all the users defined for each database on the server, it gives --the database name, the user name, the role membership, and the login
--name.
set nocount on
declare @name sysname,
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASENAME] sysname NOT NULL ,
[USER_NAME] sysname NOT NULL,
[ROLE_NAME] sysname NULL,
[LOGIN_NAME] sysname NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @sql =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name, d.name
from ' + QuoteName(@name) + '.dbo.sysusers a
left join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
left join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
left join ' + 'master.dbo.syslogins d on d.sid = a.sid
where a.name != ''dbo'' and a.issqlrole = 0'
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply