auditing script error .

• ramyours2003

  SSChampion

  Points: 12266

  More actions

  February 24, 2014 at 7:27 am

  #289868

  whil executing the script

  CREATE TABLE #DBROLES

  (

  DBName SYSNAME NOT NULL ,

  UserName SYSNAME NOT NULL ,

  db_owner VARCHAR(3) NOT NULL ,

  db_accessadmin VARCHAR(3) NOT NULL ,

  db_securityadmin VARCHAR(3) NOT NULL ,

  db_ddladmin VARCHAR(3) NOT NULL ,

  db_datareader VARCHAR(3) NOT NULL ,

  db_datawriter VARCHAR(3) NOT NULL ,

  db_denydatawriter VARCHAR(3) NOT NULL ,

  db_denydatareader VARCHAR(3) NOT NULL ,

  db_sysadmin VARCHAR(3) NOT NULL ,

  db_serveradmin VARCHAR(3) NOT NULL ,

  db_processadmin VARCHAR(3) NOT NULL ,

  db_dbcreator VARCHAR(3) NOT NULL ,

  Cur_Date DATETIME NOT NULL

  DEFAULT GETDATE()

  )

  DECLARE @dbname NVARCHAR(200)

  DECLARE @mSQL1 NVARCHAR(MAX)

  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, db_owner, db_accessadmin,

  db_securityadmin, db_ddladmin, db_datareader, db_datawriter,db_sysadmin,db_serveradmin,db_processadmin,db_dbcreator,

  db_denydatareader, db_denydatawriter)

  SELECT ' + '''' + @dbname + '''' + ' as DBName ,UserName, ' + CHAR(13)

  + '

  Max(CASE RoleName WHEN ''db_processadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_processadmin,

  Max(CASE RoleName WHEN ''db_dbcreator'' THEN ''Yes'' ELSE ''No'' END) AS db_dbcreator,

  Max(CASE RoleName WHEN ''db_serveradmin'' THEN ''Yes'' ELSE ''No'' END) AS db_serveradmin,

  Max(CASE RoleName WHEN ''db_sysadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_sysadmin,

  Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,

  Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,

  Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,

  Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,

  Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,

  Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,

  Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,

  Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter

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

  + DB_NAME() + '.dbo.sysusers c

  on a.groupuid = c.uid )s

  Group by USERName

  order by UserName'

  -- Remove the comments below if you want to execute this!

  --EXECUTE (@mSql1)

  PRINT @msql1

  FETCH NEXT FROM DBName_Cursor INTO @dbname

  END

  CLOSE DBName_Cursor

  DEALLOCATE DBName_Cursor

  Go

  --select * from #DBROLES

  --Drop Table #DBROLES

  got the error

  Msg 2715, Level 16, State 7, Line 1

  Column, parameter, or variable #1: Cannot find data type SYSNAME.

  how to slove ?