Strange error with sp_msforeachdb

Question

Post reply

Strange error with sp_msforeachdb

derek.colley

SSCrazy Eights

Points: 8040
More actions
August 16, 2012 at 7:56 am

#262950

So I'm trying to create a security dictionary - single table, containing one row per server login/database/database user/granted permission combination.
Not exactly a well-normalised design but anyway...
My code is below. I'm getting a strange error back ... and I can't spot what might have gone wrong. It reads like a stray ? somewhere. Has anyone encountered it before?
SET NOCOUNT ON
--CREATE TABLE SANDBOX_DEV.dbo.NewMapLoginsToUsersToPermissions (
--database_name VARCHAR(100),date_queried VARCHAR(11),
--login_name VARCHAR(100),username VARCHAR(100),
--permission_name VARCHAR(100),state_desc VARCHAR(100) )
EXEC sp_msforeachdb '
INSERT INTO SANDBOX_DEV.dbo.NewMapLoginsToUsersToPermissions
SELECT''?'' [database_name],
CAST(LEFT(CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),11) AS VARCHAR(11)) [date_queried],
sprin.name [login_name],
dprin.name [username], sperm.permission_name, sperm.state_desc
FROM sys.server_principals sprin
LEFT JOIN sys.database_principals dprin ON sprin.sid = dprin.sid
LEFT JOIN sys.database_permissions sperm ON dprin.principal_id = sperm.grantee_principal_id
WHERE dprin.is_fixed_role = 0 AND dprin.name NOT IN (''public''','dbo'',''guest'',''sys'', ''INFORMATION_SCHEMA'')
ORDER BY database_name ASC, login_name ASC, username ASC, permission_name ASC
'
SELECT * FROM SANDBOX_DEV.dbo.NewMapLoginsToUsersToPermissions
Msg 195, Level 15, State 10, Line 5
'GETmasterATE' is not a recognized built-in function name.
Msg 55555, Level 16, State 1, Procedure sp_MSforeach_worker, Line 92
sp_MSforeach_worker assert failed: command too long
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 1

Got it. Your list of database names isn't quite right...

you've got

AND dprin.name NOT IN (''public''','dbo'',''guest'',''sys'', ''INFORMATION_SCHEMA'')

where you should have

AND dprin.name NOT IN (''public'',''dbo'',''guest'',''sys'', ''INFORMATION_SCHEMA'')

Thomas Rushton
blog: https://thelonedba.wordpress.com

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Brilliant, thanks - didn't spot that typo there!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.