Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the
LASTLOGIN_DATE.
ALTER PROCEDURE [dbo].[ZZ_USER_ROLES]
AS
BEGIN
DECLARE @DBNAME VARCHAR(200)
DECLARE @MSQL1 VARCHAR(8000)
DROP TABLE #DBROLES
CREATE TABLE #DBROLES
( DBNAME SYSNAME NOT NULL,
USERNAME SYSNAME NOT NULL,
ROLE_NAME VARCHAR(20) NOT NULL,
LASTLOGIN_DATE DATETIME NULL )
DECLARE DBNAME_CURSOR CURSOR FOR
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME NOT IN ('MSSECURITY','TEMPDB')
ORDER BY NAME
OPEN DBNAME_CURSOR
FETCH NEXT FROM DBNAME_CURSOR INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MSQL1 = ' INSERT INTO #DBROLES ( DBNAME, USERNAME, ROLE_NAME)
SELECT '+''''+@DBNAME +''''+ ' AS DBNAME ,USERNAME, '+CHAR(13)+ '
MIN(CASE WHEN (ROLENAME = ''DB_OWNER'' OR ROLENAME = ''DB_ACCESSADMIN '' OR ROLENAME = ''DB_SECURITYADMIN'' OR ROLENAME = ''DB_DDLADMIN'' ) THEN ''1''
WHEN ROLENAME = ''DB_DATAWRITER'' THEN ''2''
WHEN ROLENAME = ''DB_DATAREADER'' THEN ''3''
ELSE ''4''
END) AS ROLE
FROM (SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME
FROM ' + @DBNAME+'.dbo.SYSMEMBERS A '+CHAR(13)+
' JOIN '+ @DBNAME+'.dbo.SYSUSERS B '+CHAR(13)+
' ON A.MEMBERUID = B.UID AND B.isntuser = 1 JOIN '+@DBNAME +'.DBO.SYSUSERS C
ON A.GROUPUID = C.UID)S
GROUP BY USERNAME
ORDER BY USERNAME'
--PRINT @MSQL1
EXECUTE (@MSQL1)
FETCH NEXT FROM DBNAME_CURSOR INTO @DBNAME
END
CLOSE DBNAME_CURSOR
DEALLOCATE DBNAME_CURSOR
SELECT DBNAME,USERNAME,CASE ROLE_NAME WHEN '1' THEN 'POWER' WHEN '2' THEN 'READWRITE' WHEN '3' THEN 'READ' WHEN '4' THEN 'NOPERMISSIONS' END ROLE,CUR_DATE FROM #DBROLES
END
I'd greatly appreciate if someone could suggest a solution and add the logic to get the last logged in date for each user in the code above.It's a bit urgent. I've looked at some articles online but did not find them to be suiting my needs. Thanks a lot!
