October 27, 2016 at 11:10 am
Hi Guys,
Please help to fix this query as it's not working for extracting object permissions for all databases when using sp_MSForEachdb.
USE MASTER
GO
BEGIN
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #TUser (
ServerNamevarchar(256),
DBNameSYSNAME,
[type_desc]varchar(256),
[name]SYSNAME NULL,
state_descvarchar(256) NULL,
permission_name varchar(256),
[Object]SYSNAME NULL,
[major_id] INT)
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT @@servername, ''?'' as DBName,
pr.type_desc, pr.name, pe.state_desc,
pe.permission_name, s.name + ''.'' + oj.name AS Object, major_id
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
JOIN sys.objects AS oj
ON oj.object_id = pe.major_id
JOIN sys.schemas AS s
ON oj.schema_id = s.schema_id
WHERE class_desc = ''OBJECT_OR_COLUMN''
ORDER BY pr.name, pr.type_desc;
'
END
SELECT *
FROM #TUser
ORDER BY DBName,
[name]
DROP TABLE #TUser
END
But it works without sp_MSForEachdb when running under the context of each db.
/*
SELECT pr.type_desc, pr.name, pe.state_desc,
pe.permission_name, s.name + '.' + oj.name AS Object, major_id
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
JOIN sys.objects AS oj
ON oj.object_id = pe.major_id
JOIN sys.schemas AS s
ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';
*/
Many thanks!
October 27, 2016 at 11:17 am
When you say it isn't working, what do you mean? It doesn't return what you expect, you are getting an error or errors?
October 27, 2016 at 11:19 am
Its not throwing any errors. I'm not getting the sql/domain user permissions, only public role permissions are extracted. Thanks!
October 27, 2016 at 11:37 am
sp_MSforeachDB does not automatically change the context to each database, so it executes in the context of the database where you originally run the query. Preface your dynamic SQL with USE [?].
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 27, 2016 at 12:00 pm
Thanks Drew.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply