October 13, 2010 at 12:38 pm
Code works great but need to it to loop through the DB's. Any help is much appreciated!
-- Process
-- Create Temp Table for Users
-- Create Temp Table for Roles
-- Populate Users
-- Populate Roles
-- Iterate though each user and update their roles into a single column
-- Return the users and their roles
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
October 13, 2010 at 12:47 pm
See attachment.
It uses sp_msforeachdb
October 13, 2010 at 12:49 pm
Excellent, anyway to put the DB names inside the output?
October 13, 2010 at 12:56 pm
SQLSeTTeR (10/13/2010)
Excellent, anyway to put the DB names inside the output?
sure....
see new script
October 13, 2010 at 1:13 pm
Thanks! Insert is failing Let me debug.
Insert Error: Column name or number of supplied values does not match table definition.
October 13, 2010 at 1:14 pm
works perfect for me 😎
October 13, 2010 at 1:16 pm
SQLSeTTeR (10/13/2010)
Thanks! Insert is failing Let me debug.Insert Error: Column name or number of supplied values does not match table definition.
weird , i added this....
Create Table #Temp_Users
(
Name varchar(128),
CreateDate datetime,
LastModifiedDate datetime,
LoginType varchar(50),
Roles varchar(1024),
DBNAME nvarchar(150)
)
so not sure whats wrong. maybe your copy and paste missed something.....
October 13, 2010 at 1:23 pm
Picture perfect my friend. Thank you very much!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply