Script to find username and corresponding loginname for all user DB

Question

Post reply

Script to find username and corresponding loginname for all user DB

Shashank Srivastava

Mr or Mrs. 500

Points: 584
More actions
April 12, 2013 at 2:42 pm

#276447

Comments posted to this topic are about the item Script to find username and corresponding loginname for all user DB
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Viewing 9 posts - 1 through 9 (of 9 total)

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

spectrumplus1 SSC Rookie Points: 32 More actions · Answer 1

we can check the username in oracle by below command

show user;

https:www.spectrumplus.in

ken.trock SSCertifiable Points: 5147 More actions · Answer 2

Nice use of MS_ForEachDB here. We don't use it much here because it's undocumented and can be a little picky with syntax. We use this type of construct to loop DBs:

DECLARE @databases TABLE(dbName VARCHAR(100))

DECLARE @CurrentDB VARCHAR(100), @SQL NVARCHAR(max)

--Exclude system DBs

INSERT @databases SELECT name from sys.databases WHERE database_id > 4

WHILE EXISTS (SELECT TOP 1 dbName FROM @databases)

BEGIN

SET@CurrentDB = (SELECT TOP 1 dbName FROM @databases)

SET @SQL = 'Use ' + @CurrentDB + '; <Take some action>'

--PRINT @sql

EXEC sp_executesql @SQL

DELETE @databases WHERE dbName = @CurrentDB

END

Ken

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 3

Very nice script, I was in fact looking for a way to pull this information together. I found a few scripts that gave me too much and some too little. Thanks for doing the heavy work for us. I'm finding this script very handy.

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 4

Shaz I made a small change to your script to work with my databases. I have a couple that have a space in the file name. No major change, just added the few brakets to the ? below.

set @Command= 'if not exists (select * from #systemdbs where name = ''[?]'') begin '+char(13)+

+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)

+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)

+'truncate table #dbusersbuffer'+char(13)

+'end'

Lee Linares SSCrazy Points: 2730 More actions · Answer 5

Nice script. Thanks for taking the time to share. I ran into one problem though. I get this error:

Msg 213, Level 16, State 7, Procedure sp_helpuser, Line 250

Insert Error: Column name or number of supplied values does not match table definition.

The reason is that sp_helpuser will return 2 result sets if aliases exist in any database.

Thanks again,

Lee

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 6

Thanks.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 7

Happy to help you. Thanks

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 8

Thank you for the modification . Yes we need to make those changes to include databases that have spaces.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Script to find username and corresponding loginname for all user DB

Cookies on SQLServerCentral