November 13, 2008 at 1:17 pm
I am trying to retrieve the information of all the login accounts created, what database mapping do they have. Does someone know what sys table or view can have this information or a statement to extract it?
Thanks,
Antonio M.
November 13, 2008 at 2:10 pm
select * from sys.logins -- to get login informations
use [name of user database]
go
select * from select * from sys.sql_logins
November 13, 2008 at 2:45 pm
Thanks. I checked this view before, it only contains the general information of the logins. I need to extract all the mapping databases the login accounts have and their accesses on each DB like db_owner, db_datareader, db_datawriter, etc, so I can save that info in a file before we migrated the databases to a new server.
November 13, 2008 at 3:10 pm
Try this:
SET NOCOUNT ON
Drop Table #Usersdetail
Create Table #Usersdetail
(
IDintidentity(1,1),
DBName sysname collate database_default Null
,DBRole sysname collate database_default Null
,MemberName sysname collate database_default Null
,MemberSID sysname collate database_default Null
)
EXEC master..sp_MSForeachdb'
BEGIN
Declare @counter int
Select @counter=count(*) from #Usersdetail
INSERT INTO #Usersdetail(DBRole,MemberName,MemberSID)
EXEC ?..sp_helprolemember
BEGIN
Update #Usersdetail set DBName=''?'' where ID>=@counter
END
END'
Select 'Use '+ DBName+char(10)+'Go'+char(10)+' sp_addrolemember '+''''+DBRole+''''+ ','+''''+MemberName+'''' from #Usersdetail
--Select LoginName, UserName, GroupName from #Usersdetail where UserName not like 'dbo' and LoginName is not NULL and groupname<>'public'
select * from #Usersdetail
DROP TABLE #Usersdetail
HTH!
MJ
November 19, 2008 at 3:45 pm
This worked. I got all the info. Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply