Syntax help for sp_MSforeachdb

[mossbs]

Hi guys,

This is possibly along similar lines to one i raised on here a couple of months ago - but with a twist...

Am trying to produce a table for all users accross all databases on a server using the sp_MSforeachdb stored procedure.

Solution was to use the following code...

CREATE TABLE ##Users
(DB VARCHAR(100),
UserName VARCHAR(100),
CreateDate DATETIME,
UpdateDate DATETIME)

EXEC master..sp_MSforeachdb 
'USE [?] 
INSERT INTO ##Users 
SELECT DB_NAME() AS DB, 
[name], createdate, 
updatedate 
FROM sysusers'

SELECT * FROM ##Users
DROP TABLE ##Users

However as this lists all database roles as well, what i wanted to do was to create a table that held the username and then the roles that user had attached to it in a seperate column.

so now i have this...

Create Table #Temp_Users
(
    Name             varchar(128),
    CreateDate        datetime,
    LastModifiedDate    datetime,
    LoginType        varchar(50),
    Roles            varchar(1024)
)
Create Table #Temp_Roles
(
    Name             varchar(128),
    Role            varchar(128)
)
insert into #Temp_Users
select Name, [Create Date] = CreateDate, [Last Modified Date] = UpdateDate, 
    LoginType = case 
        when IsNTName = 1 then 'Windows Account' 
        when IsNTGroup = 1 then 'Windows Group' 
        when isSqlUser = 1 then 'SQL Server User' 
        when isAliased =1 then 'Aliased' 
        when isSQLRole = 1 then 'SQL Role' 
        when isAppRole = 1 then 'Application Role' 
        else 'Unknown' 
        end,
    Roles = ''
from sysusers 
where SID is not null
order by Name
insert into #Temp_Roles
select MemberName = u.name, DbRole = g.name
            from sysusers u, sysusers g, sysmembers m
            where   g.uid = m.groupuid
                and g.issqlrole = 1
                and u.uid = m.memberuid
            order by 1, 2


Declare @Name    varchar(128)
Declare @Roles    varchar(1024)
Declare @Role    varchar(128)

DECLARE UserCursor CURSOR for
SELECT name from #Temp_Users
OPEN UserCursor
FETCH NEXT FROM UserCursor into @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    set @Roles = ''
    print @Name
    DECLARE RoleCursor CURSOR for
    SELECT Role from #Temp_Roles where Name = @Name
    
    OPEN RoleCursor
    FETCH NEXT FROM RoleCursor into @Role
    WHILE @@FETCH_STATUS = 0
    BEGIN
        if (@Roles > '')
            set @Roles = @Roles + ', '+@Role
        else
            set @Roles = @Role
        FETCH NEXT FROM RoleCursor into @Role
    end
    Close RoleCursor
    DEALLOCATE RoleCursor
    Update #Temp_Users set Roles = @Roles where Name = @Name
       FETCH NEXT FROM UserCursor into @Name
END
CLOSE UserCursor
DEALLOCATE UserCursor

select * from #Temp_Users

drop table #Temp_Users
drop table #Temp_Roles

What i am sturggling with is now running this latest script through the sp_MSforeachdb stored procedure.

What i have so far is...

Create Table ##Temp_Users
(DB varchar(100),
    Name             varchar(128),
    CreateDate        datetime,
    LastModifiedDate    datetime,
    LoginType        varchar(50),
    Roles            varchar(1024)
)
Create Table ##Temp_Roles
(
    Name             varchar(128),
    Role            varchar(128)
)

Declare @@Name    varchar(128)
Declare @@Roles    varchar(1024)
Declare @@Role    varchar(128)



EXEC master.sys.sp_MSforeachdb 
'USE [?] 
insert into ##Temp_Users
select DB_Name(),Name, [Create Date] = CreateDate, [Last Modified Date] = UpdateDate, 
    LoginType = case 
        when IsNTName = 1 then ''Windows Account'' 
        when IsNTGroup = 1 then ''Windows Group'' 
        when isSqlUser = 1 then ''SQL Server User'' 
        when isAliased =1 then ''Aliased'' 
        when isSQLRole = 1 then ''SQL Role'' 
        when isAppRole = 1 then ''Application Role'' 
        else ''Unknown'' 
        end,
    Roles = ''
from sysusers 
where SID is not null
order by Name
insert into ##Temp_Roles
select MemberName = u.name, DbRole = g.name
            from sysusers u, sysusers g, sysmembers m
            where   g.uid = m.groupuid
                and g.issqlrole = 1
                and u.uid = m.memberuid
            order by 1, 2




DECLARE UserCursor CURSOR for
SELECT name from ##Temp_Users
OPEN UserCursor
FETCH NEXT FROM UserCursor into @@Name
WHILE @@FETCH_STATUS = 0
BEGIN
    set @@Roles = ''
    print @@Name
    DECLARE RoleCursor CURSOR for
    SELECT Role from #Temp_Roles where Name = @@Name
    
    OPEN RoleCursor
    FETCH NEXT FROM RoleCursor into @@Role
    WHILE @@FETCH_STATUS = 0
    BEGIN
        if (@@Roles > '')
            set @@Roles = @@Roles + ', '+@@Role
        else
            set @@Roles = @@Role
        FETCH NEXT FROM RoleCursor into @@Role
    end
    Close RoleCursor
    DEALLOCATE RoleCursor
    Update ##Temp_Users set Roles = @@Roles where Name = @@Name
       FETCH NEXT FROM UserCursor into @@Name
END
CLOSE UserCursor
DEALLOCATE UserCursor'

select * from ##Temp_Users
drop table ##Temp_Users
drop table ##Temp_Roles

but this is keeps throwing error saying

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database '?'. No entry found with that name. Make sure that the name is entered correctly.

any ideas please?

Cheers.

 

[gmmastros]

The error message is misleading. Truth is, there are several errors in this code, all related to single-quotes. When writing dynamic queries or queries meant for sp_msforeachdb, I usually do this:

1. Write the query without it being dynamic. Make sure it works.
2. Highlight the query. Replace single-quote with 2 single quotes.
3. Add a single quote at the beginning and the end.
4. Put exec sp_msforeachdb in front of it.

In your case, you need to put the variable declarations inside the dynamic call, you missed a bunch of single-quote problems, and you have a temp table with a single # sign.

Create Table ##Temp_Users
(DB varchar(100),
    Name             varchar(128),
    CreateDate        datetime,
    LastModifiedDate    datetime,
    LoginType        varchar(50),
    Roles            varchar(1024)
)
Create Table ##Temp_Roles
(
    Name             varchar(128),
    Role            varchar(128)
)

EXEC sp_MSforeachdb '
use [?] 

[!]Declare @@Name    varchar(128)
Declare @@Roles    varchar(1024)
Declare @@Role    varchar(128)[/!]

insert into ##Temp_Users
select DB_Name(),Name, [Create Date] = CreateDate, [Last Modified Date] = UpdateDate, 
    LoginType = case 
        when IsNTName = 1 then ''Windows Account'' 
        when IsNTGroup = 1 then ''Windows Group'' 
        when isSqlUser = 1 then ''SQL Server User'' 
        when isAliased =1 then ''Aliased'' 
        when isSQLRole = 1 then ''SQL Role'' 
        when isAppRole = 1 then ''Application Role'' 
        else ''Unknown'' 
        end,
    Roles = [!]''''[/!]
from sysusers 
where SID is not null
order by Name
insert into ##Temp_Roles
select MemberName = u.name, DbRole = g.name
            from sysusers u, sysusers g, sysmembers m
            where   g.uid = m.groupuid
                and g.issqlrole = 1
                and u.uid = m.memberuid
            order by 1, 2




DECLARE UserCursor CURSOR for
SELECT name from ##Temp_Users
OPEN UserCursor
FETCH NEXT FROM UserCursor into @@Name
WHILE @@FETCH_STATUS = 0
BEGIN
    set @@Roles = [!]''''[/!]
    print @@Name
    DECLARE RoleCursor CURSOR for
    SELECT Role from [!]#[/!]#Temp_Roles where Name = @@Name
    
    OPEN RoleCursor
    FETCH NEXT FROM RoleCursor into @@Role
    WHILE @@FETCH_STATUS = 0
    BEGIN
        if (@@Roles > [!]''''[/!])
            set @@Roles = @@Roles + [!]'', ''[/!]+@@Role
        else
            set @@Roles = @@Role
        FETCH NEXT FROM RoleCursor into @@Role
    end
    Close RoleCursor
    DEALLOCATE RoleCursor
    Update ##Temp_Users set Roles = @@Roles where Name = @@Name
       FETCH NEXT FROM UserCursor into @@Name
END
CLOSE UserCursor
DEALLOCATE UserCursor'

select * from ##Temp_Users
drop table ##Temp_Users
drop table ##Temp_Roles

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

George - as ever - Thankyou!

I always miss the double quotes on dynamic SQL, that and the single # seem to be constant things that i overlook some how or another!

Other than that - the sequence you suggest doing it in (as in write and test it NOT dynamic) is how i did it.

Many thanks though George!

 

[gmmastros]

It's actually the 2nd step that is most helpful. I assume you write your queries in SSMS. After writing the query, highlight the entire thing, and the press CTRL-H. For "Find What" put a single-quote. For "Replace With" put 2 single-quotes. Then click Replace All.

In my opinion, getting the single quotes correct is a hige pain. The replace trick helps a lot.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

yeah i am in SSMS - and i do often use Ctrl+H for things - but never thought of using it for this - good shout!

Cheers George!