September 16, 2010 at 1:55 am
When i execute the following query i got the message.
I couldnt find out what exactly the problem is.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
DBA
Msg 208, Level 16, State 1, Line 2
Invalid object name 'DB_ROLE_09_16_2010'.
EMD
Msg 208, Level 16, State 1, Line 2
Invalid object name 'DB_ROLE_09_16_2010'.
A_Schema
Query:
DECLARE @I INT
DECLARE @C INT
DECLARE @Name VARCHAR(800)
SET @I = 1
SELECT @C = COUNT(*) FROM SYS.SYSDATABASES
SET @Name = ''
DECLARE @Table_Name VARCHAR(800)
SET @Table_Name = 'DB_ROLE_' + REPLACE(CONVERT(VARCHAR(50),GETDATE(), 110),'-','_')
EXEC ('CREATE TABLE '+@Table_Name+' (DATABASE_NAME VARCHAR(800),MEMBER_NAME VARCHAR(800), DB_ROLE VARCHAR(4000), OBJECTNAME VARCHAR(800), OBJ_ID BIGINT, HAS_SELECT VARCHAR(100),
HAS_INSERT VARCHAR(100),HAS_UPDATE VARCHAR(100),HAS_DELETE VARCHAR(100), HAS_EXECUTE VARCHAR(100)')
WHILE (@I < @C)
BEGIN
SELECT @Name = name FROM SYS.SYSDATABASES WHERE dbid = @I AND name NOT IN ('tempdb','msdb','master','model')
IF (@Name <> '')
BEGIN
PRINT @Name
EXEC ('
INSERT INTO '+@Table_Name+'
select'''+@Name+''' DATABASE_NAME,
m.name MEMBER_NAME,
g.name DB_ROLE,
null OBJECTNAME,
0 OBJ_ID,
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datareader'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_SELECT'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_INSERT'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_UPDATE'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_DELETE'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name = ''db_owner''THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_EXECUTE''
from '+@Name+'.sys.sysmembers
join '+@Name+'.sys.sysusers g
on groupuid = g.uid
join '+@Name+'.sys.sysusers m
on memberuid = m.uid
')
END
SET @I = @I + 1
END
September 16, 2010 at 2:21 am
Try this :
DECLARE @I INT
DECLARE @C INT
DECLARE @Name VARCHAR(800)
DECLARE @command varchar(max)
SET @I = 1
SELECT @C = COUNT(*) FROM SYS.SYSDATABASES
SET @Name = ''
SET @command = ''
DECLARE @Table_Name VARCHAR(800)
SET @Table_Name = 'DB_ROLE_' + REPLACE(CONVERT(VARCHAR(50),GETDATE(), 110),'-','_')
SET @command = 'CREATE TABLE '+@Table_Name+' (DATABASE_NAME VARCHAR(800),MEMBER_NAME VARCHAR(800), DB_ROLE VARCHAR(4000), OBJECTNAME VARCHAR(800), OBJ_ID BIGINT, HAS_SELECT VARCHAR(100),
HAS_INSERT VARCHAR(100),HAS_UPDATE VARCHAR(100),HAS_DELETE VARCHAR(100), HAS_EXECUTE VARCHAR(100))'
EXEC (@Command)
--PRINT @Command
SET @command = ''
WHILE (@I < @C)
BEGIN
SELECT @Name = name FROM SYS.SYSDATABASES WHERE dbid = @I AND name NOT IN ('tempdb','msdb','master','model')
IF (@Name <> '')
BEGIN
PRINT @Name
SET @command =
'
INSERT INTO '+@Table_Name+'
select '''+@Name+''' DATABASE_NAME,
m.name MEMBER_NAME,
g.name DB_ROLE,
null OBJECTNAME,
0 OBJ_ID,
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datareader'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_SELECT'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_INSERT'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_UPDATE'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name IN (''db_owner'',''db_datawriter'') THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_DELETE'',
CASE WHEN '''+@Name+''' IS NOT NULL THEN CASE WHEN g.name = ''db_owner''THEN ''Yes'' ELSE ''No'' END ELSE ''N/A'' END as ''Has_EXECUTE''
from '+@Name+'.sys.sysmembers
join '+@Name+'.sys.sysusers g
on groupuid = g.uid
join '+@Name+'.sys.sysusers m
on memberuid = m.uid
'
EXEC (@Command)
--PRINT @command
SET @command = ''
END
SET @Name = ''
SET @I = @I + 1
END
September 16, 2010 at 2:24 am
EasyBoy (9/16/2010)
When i execute the following query i got the message.I couldnt find out what exactly the problem is.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
DBA
Msg 208, Level 16, State 1, Line 2
Invalid object name 'DB_ROLE_09_16_2010'.
EMD
Msg 208, Level 16, State 1, Line 2
Invalid object name 'DB_ROLE_09_16_2010'.
A_Schema
EXEC ('CREATE TABLE '+@Table_Name+' (DATABASE_NAME VARCHAR(800),MEMBER_NAME VARCHAR(800), DB_ROLE VARCHAR(4000), OBJECTNAME VARCHAR(800), OBJ_ID BIGINT, HAS_SELECT VARCHAR(100),
HAS_INSERT VARCHAR(100),HAS_UPDATE VARCHAR(100),HAS_DELETE VARCHAR(100), HAS_EXECUTE VARCHAR(100))') -- Look here i have added an extra ) after the last VARCHAR(100)
As for the error , i have added an extra ) after the last VARCHAR(100).. See the comment line in the above code...
September 16, 2010 at 2:37 am
Thank you very much.
That was cool like your name ColdCoffee.
Greatly appreicate your help.
😀
September 16, 2010 at 2:39 am
Fantastic... thanks for the appreciation EasyBoy.. 😀
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply