These are master stored procedures and can be called from within any database.
sp_dba_reportloginuser
This queries the systems tables to determine what type of access is granted to the current database, it reports the type of account, and what roles the user is a member of. It also display the database alias for that login.
sp_dba_reportfixedserverrole
Similar to the above, but this one focuses on the fixed server roles, and is not database specific, rather it is global to the server. It queries the system tables to determine what fixed server roles each login is a member of.
T-SQL For sp_dba_reportloginuser
AS
SET NOCOUNT ON
/* Clean Up */
IF (OBJECT_ID('tempdb..#tblServerLoginUserInfo') >= 0)
DROP TABLE #tblServerLoginUserInfo
IF (OBJECT_ID('tempdb..#tblServerLoginRoleInfo') >= 0)
DROP TABLE #tblServerLoginRoleInfo
/* Create Holding/Scratch tables */
CREATE TABLE #tblServerLoginUserInfo
(
ServerLogin VARCHAR(100),
Name VARCHAR(128),
LoginType VARCHAR(50),
Roles VARCHAR(1024)
)
Create Table #tblServerLoginRoleInfo
(
Name VARCHAR(128),
Role VARCHAR(128)
)
/* Populate with Login/User information, username and account type */
INSERT INTO #tblServerLoginUserInfo
SELECT L.loginname,
sysusers.[Name],
LoginType = CASE
WHEN sysusers.IsNTName = 1 THEN 'Windows Account'
WHEN sysusers.IsNTGroup = 1 THEN 'Windows Group'
WHEN isSqlUser = 1 THEN 'SQL Server User'
WHEN isAliased =1 THEN 'Aliased'
WHEN isSQLRole = 1 THEN 'SQL Role'
WHEN isAppRole = 1 THEN 'Application Role'
ELSE 'Unknown'
END,
Roles = ''
FROM sysusers
INNER JOIN master..syslogins L on sysusers.SID = L.SID
WHERE sysusers.SID IS NOT NULL
ORDER BY sysusers.Name
/* Populate with username and role information */
INSERT INTO #tblServerLoginRoleInfo
SELECT u.name AS MemberName, g.name AS DBRole
FROM sysusers u, sysusers g, sysmembers m
WHERE g.uid = m.groupuid
AND g.issqlrole = 1
AND u.uid = m.memberuid
ORDER BY 1, 2
DECLARE @Name VARCHAR(128)
DECLARE @Roles VARCHAR(1024)
DECLARE @Role VARCHAR(128)
DECLARE UserCursor CURSOR FOR
SELECT name from #tblServerLoginUserInfo
OPEN UserCursor
FETCH NEXT FROM UserCursor into @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Roles = ''
DECLARE RoleCursor CURSOR for
SELECT Role FROM #tblServerLoginRoleInfo WHERE Name = @Name
OPEN RoleCursor
FETCH NEXT FROM RoleCursor INTO @Role
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Roles > '')
SET @Roles = @Roles + ', ' + @Role
ELSE
SET @Roles = @Role
FETCH NEXT FROM RoleCursor INTO @Role
END
CLOSE RoleCursor
DEALLOCATE RoleCursor
UPDATE #tblServerLoginUserInfo SET Roles = @Roles WHERE Name = @Name
FETCH NEXT FROM UserCursor INTO @Name
END
CLOSE UserCursor
DEALLOCATE UserCursor
SELECT ServerLogin,
Name AS 'DatabaseUser',
LoginType AS 'AccountType',
Roles AS 'MemberOf'
FROM #tblServerLoginUserInfo
WHERE ServerLogin not in ('sa')
AND Name not in ('sa', 'dbo')
SET NOCOUNT ON
GO
exec sp_ms_marksystemobject 'sp_dba_reportloginuser'
GO
T-SQL For sp_dba_reportfixedserverrole
CREATE PROCEDURE sp_dba_reportfixedserverrole
AS
SET NOCOUNT ON
/* Clean Up */
IF (OBJECT_ID('tempdb..#FixedRoleMember') >= 0)
DROP TABLE #FixedRoleMember
CREATE TABLE #FixedRoleMember
(
ServerRoleName VARCHAR(100),
Login VARCHAR(100),
LoginSID VARBINARY(5000),
Roles VARCHAR(1000)
)
INSERT INTO #FixedRoleMember (ServerRoleName, Login, LoginSID)
EXEC dbo.sp_helpsrvrolemember
DECLARE @Name varchar(1000)
DECLARE @Role VARCHAR(1000)
DECLARE @Roles VARCHAR(1000)
DECLARE UserCursor CURSOR FOR
SELECT Login from #FixedRoleMember
OPEN UserCursor
FETCH NEXT FROM UserCursor into @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Roles = ''
DECLARE RoleCursor CURSOR for
SELECT ServerRoleName FROM #FixedRoleMember WHERE Login = @Name
OPEN RoleCursor
FETCH NEXT FROM RoleCursor INTO @Role
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Roles > '')
SET @Roles = @Roles + ', ' + @Role
ELSE
SET @Roles = @Role
FETCH NEXT FROM RoleCursor INTO @Role
END
CLOSE RoleCursor
DEALLOCATE RoleCursor
UPDATE #FixedRoleMember SET Roles = @Roles WHERE Login = @Name
FETCH NEXT FROM UserCursor INTO @Name
END
CLOSE UserCursor
DEALLOCATE UserCursor
SELECT DISTINCT(Login), Roles FROM #FixedRoleMember
DROP TABLE #FixedRoleMember
SET NOCOUNT OFF
GO
exec sp_ms_marksystemobject 'sp_dba_reportfixedserverrole'
GO