March 13, 2018 at 2:53 pm
I want to execute this for Multiple DB how should I use this in Cursor
DECLARE
@note VARCHAR(2000)
,@id INT
DECLARE C_sql CURSOR FOR
SELECT '' AS [-- Notes --],
2 AS [-- print output --]
UNION
SELECT '-- [-- User Only --] --' AS [-- notes --],
3 AS [-- print output --]
FROM sys.database_principals AS rm
WHERE [type] ='U'
OPEN C_SQL
FETCH NEXT FROM tmp INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @note
FETCH NEXT FROM C_SQL INTO @note, @id
END
CLOSE C_SQL
DEALLOCATE C_SQL
March 14, 2018 at 5:50 am
This might be helpful:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 9:05 am
No it don't work
March 14, 2018 at 9:13 am
Nita Reddy - Wednesday, March 14, 2018 9:05 AMNo it don't work
My car sounds funny, what's wrong with it?
Basically, that is what saying "No it don't work" sounds like. Why doesn't it work, what error message(s) are you getting? Remember, we can't see what you see nor are we mind readers.
March 14, 2018 at 9:17 am
Looking at your code I am not even sure what it is you are trying to accomplish.
March 14, 2018 at 10:55 am
Nita Reddy - Wednesday, March 14, 2018 9:05 AMNo it don't work
What doesn't work? What, exactly, did you try? Was there an error message? Please post your exact code. We can't see what you see...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 11:04 am
Its only giving result for one database and not other
DECLARE
@note VARCHAR(2000)
,@id INT
,@command varchar (2000)
DECLARE C_sql CURSOR FOR
SELECT '' AS [-- Notes --],
2 AS [-- print output --]
UNION
SELECT '-- [-- User Only --] --' AS [-- notes --],
3 AS [-- print output --]
FROM sys.database_principals AS rm
WHERE [type] ='U'
OPEN C_SQL
FETCH NEXT FROM tmp INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @note
FETCH NEXT FROM C_SQL INTO @note, @id
END
EXEC sp_MSforeachdb @command
CLOSE C_SQL
DEALLOCATE C_SQL
March 14, 2018 at 11:10 am
Well what is in @command?
March 14, 2018 at 11:11 am
Nita Reddy - Wednesday, March 14, 2018 11:04 AMIts only giving result for one database and not other
DECLARE
@note VARCHAR(2000)
,@id INT
,@command varchar (2000)DECLARE C_sql CURSOR FOR
SELECT '' AS [-- Notes --],
2 AS [-- print output --]UNION
SELECT '-- [-- User Only --] --' AS [-- notes --],
3 AS [-- print output --]
FROM sys.database_principals AS rm
WHERE [type] ='U'
OPEN C_SQL
FETCH NEXT FROM tmp INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @note
FETCH NEXT FROM C_SQL INTO @note, @id
END
EXEC sp_MSforeachdb @commandCLOSE C_SQL
DEALLOCATE C_SQL
Not sure how your code runs. I copy and pasted exactly what you posted and tried to run it and got the following error message:
Msg 16916, Level 16, State 1, Line 16
A cursor with the name 'tmp' does not exist.
So, what are you trying to do?
March 14, 2018 at 11:49 am
DECLARE
@note VARCHAR(2000)
,@id INT
,@command varchar (2000)
DECLARE C_sql CURSOR FOR
SELECT '' AS [-- Notes --],
2 AS [-- print output --]
UNION
SELECT '-- [-- User Only --] --' AS [-- notes --],
3 AS [-- print output --]
FROM sys.database_principals AS rm
WHERE [type] ='U'
OPEN C_SQL
FETCH NEXT FROM C_SQL INTO @note, @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @note
FETCH NEXT FROM C_SQL INTO @note, @id
END
EXEC sp_MSforeachdb @command
CLOSE C_SQL
DEALLOCATE C_SQL
March 14, 2018 at 11:49 am
This is the code is working for Single Database, I want this for Multiple DB
DECLARE
@note VARCHAR(2048)
,@id INT
,@command varchar(2000)
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 s
on z.major_id = s.schema_id
inner join sys.database_principals 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
PRINT @note
FETCH NEXT FROM C_SQL INTO @note, @id
END
EXEC sp_MSforeachdb @command
CLOSE C_SQL
DEALLOCATE C_SQL
March 14, 2018 at 12:14 pm
Nita Reddy - Wednesday, March 14, 2018 11:49 AMThis is the code is working for Single Database, I want this for Multiple DBDECLARE
@note VARCHAR(2048)
,@id INT
,@command varchar(2000)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_idWHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 2
AND [usr].[type] = 'U'
UNIONSELECT '',
2UNION
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 s
on z.major_id = s.schema_id
inner join sys.database_principals 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
PRINT @note
FETCH NEXT FROM C_SQL INTO @note, @id
END
EXEC sp_MSforeachdb @commandCLOSE C_SQL
DEALLOCATE C_SQL
Well, your code is simple wrong. Personally, I don't use sp_MSforeachdb when I need to run code over multiple databases on a SQL Server instance. From what I can tell, you are calling the undocumented procedure incorrectly. Try reading the following and it may help you figure out what you are doing wrong:
http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx
March 14, 2018 at 12:20 pm
Okay so if that's the code you want to run in every database you need to actually pass it into sp_MSforeachdb, what you have now is just running the code in the current database and for each row it returns calling sp_MSforeachdb with an empty command, which does nothing. I'm not going to rewrite your entire chunk of code but you would need to do something like this.
DECLARE @command varchar(max)
SET @command = 'USE ?;
DECLARE @testy varchar(30);
DECLARE TEMP_CON CURSOR FOR SELECT CAST(COUNT(*) AS varchar) AS COL_ONE FROM sys.tables
OPEN TEMP_CON
FETCH NEXT FROM TEMP_CON INTO @testy
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @testy;
FETCH NEXT FROM TEMP_CON INTO @TESTY
END
CLOSE TEMP_CON
DEALLOCATE TEMP_CON'
EXEC sp_MSforeachdb @command
March 14, 2018 at 12:23 pm
I think the cart has come before the horse. I suspect what is needed is that the cursor SQL needs to be the contents of the @command variable.
EDIT: looks like ZZartin beat me to it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 12:25 pm
sgmunson - Wednesday, March 14, 2018 12:23 PMI think the cart has come before the horse. I suspect what is needed is that the cursor SQL needs to be the contents of the @command variable.
Never mind, what I saw was probably the PRINT @note output.
Still the EXEC of MSforeachdb is wrong.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply