List all Usernames & Roles for all the databases in SQL 2000

Question

Post reply

List all Usernames & Roles for all the databases in SQL 2000

KDASQL

Ten Centuries

Points: 1103
More actions
August 28, 2008 at 7:07 pm

#191940

Can anyone please help me find a script that will give me a list of all the usernames and roles for all databases in SQL 2000
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Try this:

declare @sql varchar(8000)

Declare @DB_Objects varchar(8000)

Select @DB_Objects = ' name COLLATE DATABASE_DEFAULT as [Name], uid, status, sid, createdate, updatedate

From %D%.dbo.sysusers

'

Select @sql = 'SELECT * 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'

Select @sql = @sql + ') oo '

print @sql

EXEC (@sql)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

KDASQL Ten Centuries Points: 1103 More actions · Answer 2

Ran it in SQL 2000, got the following error:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'MAX'.

Server: Msg 137, Level 15, State 1, Line 6

Must declare the variable '@DB_Objects'.

Server: Msg 137, Level 15, State 1, Line 8

Must declare the variable '@DB_objects'.

Server: Msg 137, Level 15, State 1, Line 12

Must declare the variable '@sql'.

Server: Msg 137, Level 15, State 1, Line 17

Must declare the variable '@sql'.

Server: Msg 137, Level 15, State 1, Line 19

Must declare the variable '@sql'.

Server: Msg 137, Level 15, State 1, Line 20

Must declare the variable '@sql'.

Please advise.

Thank you.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

OK, this should correct thos errors:

declare @sql varchar(8000)

Declare @DB_Objects varchar(8000)

Select @DB_Objects = ' name COLLATE DATABASE_DEFAULT as [Name], uid, status, sid, createdate, updatedate

From %D%.dbo.sysusers

'

Select @sql = 'SELECT * 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'

Select @sql = @sql + ') oo '

print @sql

EXEC (@sql)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

KDASQL Ten Centuries Points: 1103 More actions · Answer 4

Now getting the following error:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'name'.