November 19, 2015 at 10:59 am
I run the following statement but I get the same error message:
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "imp.name" could not be bound.
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "imp.name" could not be bound.
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "imp.name" could not be bound.
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "imp.name" could not be bound.
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "imp.name" could not be bound.
EXEC
sp_MSforeachdb'
USE ?
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],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Database user
sys.database_principals AS princ
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
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]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN (''S'',''U'',''G'')
-- No need for these system accounts
AND princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')
UNION
--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
[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],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp2.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--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]
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]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp2 ON imp2.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN (''S'',''U'',''G'')
-- No need for these system accounts
AND membprinc.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')
UNION
--3) List all access provisioned to the public role, which everyone gets by default
SELECT
[UserType] = ''{All Users}'',
[DatabaseUserName] = ''{All Users}'',
[LoginName] = ''{All Users}'',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp3.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END,
[ColumnName] = col.[name]
FROM
--Roles
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]
--All objects
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]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
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],
[Role],
[Schema],
[ObjectName],
[ColumnName],
[PermissionType],
[PermissionState],
[ObjectType]'
Thank you!
November 19, 2015 at 1:59 pm
There are a couple problems.
The first is that spMSforeachdb takes the command as a parameter declared as nvarchar(2000). Your command is much longer than that, so it's getting truncated, resulting in the syntax errors.
The second is that each query being UNIONed needs the same number of columns, and you only have the database name as a column in the first query.
There are probably better ways of getting at the information you're after, but to do this with just a couple quick changes would mean switching from sp_MSforeachdb to using dynamic SQL created by querying sys.databases, and making sure none of the strings get truncated.
Something like this should do the trick for a quick fix:
DECLARE @sql NVARCHAR(MAX)=''
SELECT @sql=@sql+'USE '+QUOTENAME(name)+'; '+
CAST('
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] ,
[Role] = NULL ,
[PermissionType] = perm.[permission_name] ,
[PermissionState] = perm.[state_desc] ,
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END ,
[Schema] = objschem.[name] ,
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END ,
[ColumnName] = col.[name]
FROM --Database user
sys.database_principals AS princ --Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
--Permissions
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]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE princ.[type] IN ( ''S'', ''U'', ''G'' )
-- No need for these system accounts
AND princ.[name] NOT IN ( ''sys'', ''INFORMATION_SCHEMA'' )
UNION
--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
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] ,
[Role] = roleprinc.[name] ,
[PermissionType] = perm.[permission_name] ,
[PermissionState] = perm.[state_desc] ,
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END ,
[Schema] = objschem.[name] ,
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp2.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END ,
[ColumnName] = col.[name]
FROM --Role/member associations
sys.database_role_members AS members --Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
--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]
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]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
LEFT JOIN sys.database_principals AS imp2 ON imp2.[principal_id] = perm.[major_id]
WHERE membprinc.[type] IN ( ''S'', ''U'', ''G'' )
-- No need for these system accounts
AND membprinc.[name] NOT IN ( ''sys'', ''INFORMATION_SCHEMA'' )
UNION
--3) List all access provisioned to the public role, which everyone gets by default
SELECT DB_NAME() AS DatabaseName ,
[UserType] = ''{All Users}'' ,
[DatabaseUserName] = ''{All Users}'' ,
[LoginName] = ''{All Users}'' ,
[Role] = roleprinc.[name] ,
[PermissionType] = perm.[permission_name] ,
[PermissionState] = perm.[state_desc] ,
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END ,
[Schema] = objschem.[name] ,
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name] -- Schemas
WHEN 4 THEN imp3.[name] -- Impersonations
ELSE OBJECT_NAME(perm.[major_id]) -- General objects
END ,
[ColumnName] = col.[name]
FROM --Roles
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]
--All objects
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]
--Table columns
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
--Impersonations
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] ,
[Role] ,
[Schema] ,
[ObjectName] ,
[ColumnName] ,
[PermissionType] ,
[PermissionState] ,
[ObjectType]; ' AS NVARCHAR(MAX))
FROM sys.databases
EXEC(@sql)
Cheers!
November 20, 2015 at 1:27 am
Thank you very much that seems to have worked!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply