November 26, 2015 at 12:48 am
Dear All,
I'm trying to have one script which would display a result coming from different databases in one whole at the moment I get the column names for each result but I'm trying to display the column names only once e.g. Like the following result:
DatabaseName column1 column2.....
Master Firstresult Firstresult
Model SecondResult etc...
Would it be possible please?
Thank you in advance!
November 26, 2015 at 2:49 am
Are the columns the same from each DB?
If so you want union/union all
November 26, 2015 at 2:54 am
Thank you for your help!
November 27, 2015 at 1:04 am
I tried to run it on the following script but I get an error message:
create table #temp(
DatabaseName varchar(100),
[UserType] varchar(100),
[DatabaseUserName] varchar(100),
[LoginName] varchar(100),
[PermissionType] varchar(100),
[PermissionState] varchar(100),
[ObjectType] varchar(100),
[Schema] varchar(100),
[ObjectName] varchar(100),
[ColumnName] varchar(100)
)
EXEC sp_MSforeachdb
'USE ?
INSERT INTO #temp
SELECT DB_NAME() AS DatabaseName,
[UserType] = CASE princ.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
WHEN ''G'' THEN ''Windows Group''
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END ,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp.[name]
ELSE OBJECT_NAME(perm.[major_id])
END,
[ColumnName] = col.[name]
FROM
sys.database_principals AS princ
LEFT JOIN sys.server_principals AS ulogin ON ulogin.sid = princ.sid
LEFT JOIN sys.database_permissions AS perm ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN sys.schemas AS permschem ON permschem.schema_id = perm.major_id
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN (''S'',''U'',''G'')
AND princ.[name] NOT IN (''sys'',''INFORMATION_SCHEMA'')
UNION
SELECT DB_NAME() AS DatabaseName,
[UserType] = CASE membprinc.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
WHEN ''G'' THEN ''Windows Group''
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp2.[name]
ELSE OBJECT_NAME(perm.[major_id])
END,
[ColumnName] = col.[name]
FROM
sys.database_role_members AS members
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp2 ON imp2.[principal_id] = perm.[major_id]
WHERE membprinc.[type] IN (''S'',''U'',''G'' )
AND membprinc.[name] NOT IN (''sys'',''INFORMATION_SCHEMA'')
UNION
SELECT DB_NAME() AS DatabaseName,
[UserType] = ''{All Users}'',
[DatabaseUserName] = ''{All Users}'',
[LoginName] = ''{All Users}'',
[PermissionType] = perm.[permission_name] ,
[PermissionState] = perm.[state_desc] ,
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp3.[name]
ELSE OBJECT_NAME(perm.[major_id])
END ,
[ColumnName] = col.[name]
FROM
sys.database_principals AS roleprinc --Role permissions
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp3 ON imp3.[principal_id] = perm.[major_id]
WHERE roleprinc.[type] = ''R''
AND roleprinc.[name] = ''public''
AND obj.[is_ms_shipped] = 0
ORDER BY [UserType],
[DatabaseUserName],
[LoginName],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType];
'
select DatabaseName,[UserType],[DatabaseUserName],[LoginName],[PermissionType],[PermissionState],[ObjectType],[Schema],
[ObjectName], [ColumnName]
from
#temp
drop table #temp
Thank you!
November 27, 2015 at 1:12 am
Care to post the error?
November 27, 2015 at 1:33 am
Sorry, here is the error message:
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
Msg 105, Level 15, State 1, Line 41
Unclosed quotation mark after the character string 'Win'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Win'.
(0 row(s) affected)
I cannot see that there is anything which is missing.
Thank you!
November 27, 2015 at 1:43 am
Write the query to get it working on one DB first, then find and replace all ' with ''
November 27, 2015 at 2:10 am
It works fine when it is just 1 query but when I add more queries then that is when I get the error messages, I tried the following query and it works but I need to add more queries for my results to be correct, would it be to do with "UNION"?:
create table #temp (
DatabaseName varchar(100),
[UserType] varchar(100),
[DatabaseUserName] varchar(100),
[LoginName] varchar(100),
[PermissionType] varchar(100),
[PermissionState]varchar(100),
[ObjectType] varchar(100),
[Schema] varchar(100),
[ObjectName] varchar(100),
[ColumnName] varchar(100)
)
EXEC sp_MSforeachdb
'USE ?
INSERT INTO #temp
SELECT DB_NAME() AS DatabaseName,
[UserType] = CASE princ.[type]
WHEN "S" THEN "SQL User"
WHEN "U" THEN "Windows User"
WHEN "G" THEN "Windows Group"
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END ,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp.[name]
ELSE OBJECT_NAME(perm.[major_id])
END,
[ColumnName] = col.[name]
FROM
sys.database_principals AS princ
LEFT JOIN sys.server_principals AS ulogin ON ulogin.sid = princ.sid
LEFT JOIN sys.database_permissions AS perm ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN sys.schemas AS permschem ON permschem.schema_id = perm.major_id
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN ("S","U","G")
AND princ.[name] NOT IN ("sys","INFORMATION_SCHEMA")
ORDER BY [UserType],
[DatabaseUserName],
[LoginName],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]; '
select
DatabaseName,
[UserType],
[DatabaseUserName],
[LoginName],
[PermissionType],
[PermissionState],
[ObjectType],
[Schema],
[ObjectName],
[ColumnName]
from #temp
drop table #temp
November 27, 2015 at 2:55 am
No its not to do with UNION, its to do with how big your query is.
SP_MSFOREACHDB has a maximum length at 2000 characters or if quoted_identifies is on its 128.
Dont use undocumented features your setting yourself up for a fall should MS remove them, they are not documented so they dont tell us if they are going to be removed, you may upgrade one day to find your scripts dont work any more.
November 27, 2015 at 8:15 am
anthony.green (11/27/2015)
Dont use undocumented features your setting yourself up for a fall should MS remove them, they are not documented so they dont tell us if they are going to be removed, you may upgrade one day to find your scripts dont work any more.
Do you use Ola's code? If so, you're using undocumented features.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2015 at 8:34 am
Jeff Moden (11/27/2015)
anthony.green (11/27/2015)
Dont use undocumented features your setting yourself up for a fall should MS remove them, they are not documented so they dont tell us if they are going to be removed, you may upgrade one day to find your scripts dont work any more.Do you use Ola's code? If so, you're using undocumented features.
Yes I have used Ola's code in the past.
I was aware of the xp_fileexists and xp_delete_file procedures being used in the scripts, but not of any other. If I remember correctly this is how maintenance plans also delete files, so guessing this wont change anytime soon, but yes noted that its using undocumented features.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply