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


Comments

Guest Author: Howard Rothenburg06 Feb 2013

--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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: