March 14, 2018 at 12:48 pm
Might want to try it this way:DECLARE @command AS varchar(2000) = '
USE ?
PRINT ''--=========================================================================='';
PRINT ''USE ?'';
PRINT ''--=========================================================================='';
DECLARE @note AS varchar(2048), @id AS int;
DECLARE C_sql CURSOR FOR
SELECT
CASE
WHEN perm.state <> ''W'' THEN perm.state_desc
ELSE ''GRANT''
END +
SPACE(1) + perm.permission_name +
SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default + --TO <user name>
CASE
WHEN perm.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END
AS [SQL ],
1 AS status
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 2
AND [usr].[type] = ''U''
UNION
SELECT '''', 2
UNION
SELECT '''', 3
UNION
SELECT
CASE
WHEN z.state <> ''W'' THEN z.state_desc
ELSE ''GRANT''
END +
SPACE(1) + z.permission_name +
SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
QUOTENAME(SCHEMA_NAME(major_id)) +
SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
CASE
WHEN z.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END AS [test],
4
FROM sys.database_permissions AS z
INNER JOIN sys.schemas AS s
ON z.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS x
ON z.grantee_principal_id = x.principal_id
WHERE class = 3; --class 3 = schema
OPEN C_SQL;
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
IF LTRIM(RTRIM(@note)) <> ''''
BEGIN
PRINT @note;
END;
FETCH NEXT FROM C_SQL INTO @note, @id
END;
CLOSE C_SQL;
DEALLOCATE C_SQL;';
EXEC sp_MSforeachdb @command;
EDIT: Fix it to actually be a runnable script.result.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 1:00 pm
sgmunson - Wednesday, March 14, 2018 12:48 PMMight want to try it this way:DECLARE @command AS varchar(2000) = '
USE ?PRINT ''--=========================================================================='';
PRINT ''USE ?'';
PRINT ''--=========================================================================='';DECLARE @note AS varchar(2048), @id AS int;
DECLARE C_sql CURSOR FOR
SELECT
CASE
WHEN perm.state <> ''W'' THEN perm.state_desc
ELSE ''GRANT''
END +
SPACE(1) + perm.permission_name +
SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default + --TO <user name>
CASE
WHEN perm.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END
AS [SQL ],
1 AS status
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 2
AND [usr].[type] = ''U''
UNION
SELECT '''', 2
UNION
SELECT '''', 3
UNION
SELECT
CASE
WHEN z.state <> ''W'' THEN z.state_desc
ELSE ''GRANT''
END +
SPACE(1) + z.permission_name +
SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
QUOTENAME(SCHEMA_NAME(major_id)) +
SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
CASE
WHEN z.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END AS [test],
4
FROM sys.database_permissions AS z
INNER JOIN sys.schemas AS s
ON z.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS x
ON z.grantee_principal_id = x.principal_id
WHERE class = 3; --class 3 = schemaOPEN C_SQL;
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
IF LTRIM(RTRIM(@note)) <> ''''
BEGIN
PRINT @note;
END;
FETCH NEXT FROM C_SQL INTO @note, @id
END;CLOSE C_SQL;
DEALLOCATE C_SQL;';
EXEC sp_MSforeachdb @command;
EDIT: Fix it to actually be a runnable script.result.
I am getting Msg 102, Level 15, State 1, Line 59
Incorrect syntax near '>'.
March 14, 2018 at 1:19 pm
Runs fine on my machine. Not sure where the trouble is... Any chance of an accidental keystroke that messed with your copy after you pasted it into SSMS ?
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 1:28 pm
sgmunson - Wednesday, March 14, 2018 12:48 PMMight want to try it this way:DECLARE @command AS varchar(2000) = '
USE ?PRINT ''--=========================================================================='';
PRINT ''USE ?'';
PRINT ''--=========================================================================='';DECLARE @note AS varchar(2048), @id AS int;
DECLARE C_sql CURSOR FOR
SELECT
CASE
WHEN perm.state <> ''W'' THEN perm.state_desc
ELSE ''GRANT''
END +
SPACE(1) + perm.permission_name +
SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default + --TO <user name>
CASE
WHEN perm.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END
AS [SQL ],
1 AS status
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 2
AND [usr].[type] = ''U''
UNION
SELECT '''', 2
UNION
SELECT '''', 3
UNION
SELECT
CASE
WHEN z.state <> ''W'' THEN z.state_desc
ELSE ''GRANT''
END +
SPACE(1) + z.permission_name +
SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
QUOTENAME(SCHEMA_NAME(major_id)) +
SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
CASE
WHEN z.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END AS [test],
4
FROM sys.database_permissions AS z
INNER JOIN sys.schemas AS s
ON z.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS x
ON z.grantee_principal_id = x.principal_id
WHERE class = 3; --class 3 = schemaOPEN C_SQL;
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
IF LTRIM(RTRIM(@note)) <> ''''
BEGIN
PRINT @note;
END;
FETCH NEXT FROM C_SQL INTO @note, @id
END;CLOSE C_SQL;
DEALLOCATE C_SQL;';
EXEC sp_MSforeachdb @command;
EDIT: Fix it to actually be a runnable script.result.
I re copied and still same error ( IF LTRIM(RTRIM(@note)) <> '''')
Line 59
March 14, 2018 at 1:52 pm
Nita Reddy - Wednesday, March 14, 2018 1:28 PMsgmunson - Wednesday, March 14, 2018 12:48 PMMight want to try it this way:DECLARE @command AS varchar(2000) = '
USE ?PRINT ''--=========================================================================='';
PRINT ''USE ?'';
PRINT ''--=========================================================================='';DECLARE @note AS varchar(2048), @id AS int;
DECLARE C_sql CURSOR FOR
SELECT
CASE
WHEN perm.state <> ''W'' THEN perm.state_desc
ELSE ''GRANT''
END +
SPACE(1) + perm.permission_name +
SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default + --TO <user name>
CASE
WHEN perm.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END
AS [SQL ],
1 AS status
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 2
AND [usr].[type] = ''U''
UNION
SELECT '''', 2
UNION
SELECT '''', 3
UNION
SELECT
CASE
WHEN z.state <> ''W'' THEN z.state_desc
ELSE ''GRANT''
END +
SPACE(1) + z.permission_name +
SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
QUOTENAME(SCHEMA_NAME(major_id)) +
SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
CASE
WHEN z.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END AS [test],
4
FROM sys.database_permissions AS z
INNER JOIN sys.schemas AS s
ON z.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS x
ON z.grantee_principal_id = x.principal_id
WHERE class = 3; --class 3 = schemaOPEN C_SQL;
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
IF LTRIM(RTRIM(@note)) <> ''''
BEGIN
PRINT @note;
END;
FETCH NEXT FROM C_SQL INTO @note, @id
END;CLOSE C_SQL;
DEALLOCATE C_SQL;';
EXEC sp_MSforeachdb @command;
EDIT: Fix it to actually be a runnable script.result.
I re copied and still same error ( IF LTRIM(RTRIM(@note)) <> '''')
Line 59
Change the @command variable declaration to varchar(4000). The code is too long for 2000 if you paste it, because the tabs I put in get translated to spaces. Also, be sure to take out all those spaces and replace with the intended tabs. Once I did that, copy and paste from that post finally worked.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 2:54 pm
sgmunson - Wednesday, March 14, 2018 1:52 PMNita Reddy - Wednesday, March 14, 2018 1:28 PMsgmunson - Wednesday, March 14, 2018 12:48 PMMight want to try it this way:DECLARE @command AS varchar(2000) = '
USE ?PRINT ''--=========================================================================='';
PRINT ''USE ?'';
PRINT ''--=========================================================================='';DECLARE @note AS varchar(2048), @id AS int;
DECLARE C_sql CURSOR FOR
SELECT
CASE
WHEN perm.state <> ''W'' THEN perm.state_desc
ELSE ''GRANT''
END +
SPACE(1) + perm.permission_name +
SPACE(1) + ''TO'' + SPACE(1) + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default + --TO <user name>
CASE
WHEN perm.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END
AS [SQL ],
1 AS status
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 2
AND [usr].[type] = ''U''
UNION
SELECT '''', 2
UNION
SELECT '''', 3
UNION
SELECT
CASE
WHEN z.state <> ''W'' THEN z.state_desc
ELSE ''GRANT''
END +
SPACE(1) + z.permission_name +
SPACE(1) + ''ON'' + SPACE(1) + class_desc + ''::'' COLLATE database_default +
QUOTENAME(SCHEMA_NAME(major_id)) +
SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default +
CASE
WHEN z.state <> ''W'' THEN SPACE(0)
ELSE SPACE(1) + ''WITH GRANT OPTION''
END AS [test],
4
FROM sys.database_permissions AS z
INNER JOIN sys.schemas AS s
ON z.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS x
ON z.grantee_principal_id = x.principal_id
WHERE class = 3; --class 3 = schemaOPEN C_SQL;
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
IF LTRIM(RTRIM(@note)) <> ''''
BEGIN
PRINT @note;
END;
FETCH NEXT FROM C_SQL INTO @note, @id
END;CLOSE C_SQL;
DEALLOCATE C_SQL;';
EXEC sp_MSforeachdb @command;
EDIT: Fix it to actually be a runnable script.result.
I re copied and still same error ( IF LTRIM(RTRIM(@note)) <> '''')
Line 59
Change the @command variable declaration to varchar(4000). The code is too long for 2000 if you paste it, because the tabs I put in get translated to spaces. Also, be sure to take out all those spaces and replace with the intended tabs. Once I did that, copy and paste from that post finally worked.
Thanks for all your time
March 15, 2018 at 6:16 am
So, it worked then?
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy