Query to list all Users or Login Names from SQL database
Description
Query to list all Users or Login Names from SQL database
This will be very useful when you want to know who all are having access to the database.
Execute the below stored procedure,
sp_helpuser
Out put of this stored procedure will have following information,
UserName, GroupName, LoginName, DefDBName, UserID, SID
Follow
--List Users and Logins
DECLARE @TSQL NVARCHAR(MAX);
-- What we'll return back and the first table we'll use, syslogins
SET @TSQL =
'SELECT Database_Name, d.name COLLATE DATABASE_DEFAULT as ''User'', s.name AS ''Login''
FROM sys.server_principals s
INNER JOIN (';
SELECT @TSQL = @TSQL + 'SELECT ''' + NAME +
''' AS Database_Name,
sid, name FROM [' + NAME + '].sys.database_principals UNION ALL '
FROM sys.databases
WHERE NAME NOT IN ('master', 'tempdb');
-- Remove the final UNION ALL and completing the join
SET @TSQL = LEFT(@TSQL, LEN(@TSQL) - 10) + ') d ON s.sid = d.sid;';
EXECUTE sp_executesql @TSQL;
select l.loginname as [login name],u.name as [user name] from sysusers u inner join master..syslogins l
on u.sid=l.sid