Find all DBOs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find all DBOs

Author

Topic

devils3cups
Starting Member

23 Posts

Posted - 2008-05-29 : 09:05:43

Lets see if you guys can help me out with a solution to my problem.

Here's the project:
I need to traverse through hundreds of servers and into each of the databases and come back with a list of all the DBO's. Ideally the list would be in an excel sheet with the server,database,and the dbos.

Here's what I was thinking:
Using OSQL I created a batch script, changed for each database, to bring back the data into a text file that I'll be able to put into excel. Here's a sample of the script

echo PCSV-WHS06.dbo.RCASTER >> output.txt
osql -S PCSV-WHS06 -E -Q "use RCASTER; EXEC sp_helprolemember 'db_owner';" >> output.txt

The problem:
When the text file gets loaded it comes back formatted horribly and it would take me days to go through the list to make it look better. Should I concentrate on formatting the output or should I go about this a different way? Any suggestion helps.

devils3cups
Starting Member

23 Posts

Posted - 2008-05-30 : 11:00:46

Bump.
Anyone?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-05-30 : 11:17:26

Post a sample of your text file so we can see what's getting dumped there..

-----
try @@ECHO OFF as the first line of your batch file if your commands are getting included.....

-------------
Charlie

devils3cups
Starting Member

23 Posts

Posted - 2008-05-30 : 11:18:51

PCSV-WHS06.dbo.RCASTER
DbRole

MemberName

MemberSID

------------------------------------------------------------------------------
--------------------------------------------------
-----------------------------------------------------------------------
---------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
------------------------------
db_owner

dbo

0x01

db_owner

RCASTER

0xFAE0B9330B82EE4B9BF14871C19F0C4E

(2 rows affected)
PCSV-WHS06.dbo.RESGOV
DbRole

MemberName

MemberSID

------------------------------------------------------------------------------
--------------------------------------------------
-----------------------------------------------------------------------
---------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
------------------------------
db_owner

dbo

0x01

(1 row affected)
PCSV-WHS06.dbo.SAPStage
DbRole

MemberName

MemberSID

------------------------------------------------------------------------------
--------------------------------------------------
-----------------------------------------------------------------------
---------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
------------------------------
db_owner

dbo

0x01

db_owner

RCASTER

0xFAE0B9330B82EE4B9BF14871C19F0C4E

(2 rows affected)
PCSV-WHS06.dbo.ScoreNet
DbRole

MemberName

MemberSID

------------------------------------------------------------------------------
--------------------------------------------------
-----------------------------------------------------------------------
---------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
------------------------------
db_owner

AppsOnly

0xE67212613D95674AB5F7676711F337E2

db_owner

AppsOnlyLink

0x271ECA8BBF51614391173017D656379E

db_owner

dbo

0x01

db_owner

Impact21

0x295D1883E053624AA7F5F479D6188734

db_owner

logship

0x5E988EB45BD5DC4CB7B59A78EB8EF6ED

db_owner

SCDMST1\ZOCRN1

0x010500000000000515000000034601797354E65E5003516181080000

db_owner

SQLUsers_RAMS_A

0x010500000000000515000000034601797354E65E500351617D690000

db_owner

sunocodts

0xFE2DF9828F56F2478E43C11EB7B2F600

db_owner

Z01ESJ

0x010500000000000515000000034601797354E65E50035161F9930000

db_owner

ZS7950

0x010500000000000515000000034601797354E65E5003516129140000

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-05-30 : 11:30:06

I Guess you could try changing the sql used by osql to something like.


SET NOCOUNT ON

CREATE TABLE #dump (
    dbRole sysname
    , MemberName sysname
    , MemberSID varbinary(85)
    )

INSERT #dump
EXEC sp_helprolemember 'db_owner'

SELECT dbRole + ', ' + MemberName
FROM #dump

DROP TABLE #dump

Then the only thing that would go into your output text file would be a comma sepeerated list of roles and members

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-05-30 : 11:37:02

Also, you could ditch your
echo PCSV-WHS06.dbo.RCASTER >> output.txt

And do a


SELECT db_Name(), dbRole, MemberName FROM #dump instead

-------------
Charlie

devils3cups
Starting Member

23 Posts

Posted - 2008-05-30 : 11:49:03

Those seem like the answers I'm looking for. I'll give it a try and let you know.
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources