Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
3  
I don't see this stored anywhere by default. You would need to record it yourself via a DDL login trigger I think (though you could also extract it from the default trace if prepared to lose the info when the trace files rollover) –  Martin Smith Sep 26 '12 at 18:06
2  
@Martin I think you'd need to set up a trace specifically for this - successful logins are not recorded in the default trace. Also just for clarity DDL triggers and logon triggers are different things. :-) –  Aaron Bertrand Sep 26 '12 at 18:55
    
@AaronBertrand - Quite impressed that I managed to make two errors in such a small amount of text! Thanks for the corrections. –  Martin Smith Sep 26 '12 at 19:00
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.