Can the below code converted to Powershell ..any suggestions ,thanks.
-----------To get Users DB Role ----------------------------------------------------------------------------------------
Declare DBUser_Cur cursor for
Select name from master.dbo.sysdatabases
where Name not in ('tempdb','Pubs','Northwind','Model')
For read only
Open DBUser_Cur
Fetch next from DBUser_Cur into @DBName
While @@fetch_status = 0
Begin
--To get DB info
If (Select isnull(object_id('tempdb..#DBUser_08'), 0)) = 0
Create table #DBUser_08 (
UserName varchar(150) null,
RoleName Varchar(1000) null,
LoginName Varchar(200) null,
DefDB Varchar(1000) null,
DefSchemaName varchar(500) null,
UserID int null,
SUserID int null
)
Truncate table #DBUser_08
Declare @DBQuery varchar(200)
Set @DBQuery = @DBName+'.dbo.sp_helpuser'
Insert into #DBUser_08 exec (@DBQuery)
Declare GRPName_CUR cursor for
Select UserName, RoleName, LoginName from #DBUser_08
where (UserName is not null and UserName not like '%##%' and UserName not in ('guest','sys','INFORMATION_SCHEMA') and UserName not like '%$%')
For read only
Open GRPName_CUR
Fetch Next from GRPName_CUR into @UserName,@RoleName, @LoginName
While @@fetch_status = 0
Begin
If @LoginName is null -- To get Orphaned Users Info
Begin
select @DateCreated = ''
Set @UserStatus = 'InActive'
Select DBRole from LoginReport where UserName =@UserName and DBName =@DBName
If (@@ROWCOUNT =0)
Begin
Insert into IBM_DBHEALTH.dbo.LoginReport
(ServerName,[SQLInstanceName],[DBType],DBName,UserName,[UserStatus],DBRole,DateCreated)
values(@ServerName,@SQLInstanceName,@DBType,@DBName,@UserName,@UserStatus,@RoleName,@DateCreated)
End
Else
Begin
Select @DBCurrentRole = DBRole from LoginReport
where [UserName] = @UserName
and DBName =@DBName
Set @DBNewRole = @DBCurrentRole +'\'+@RoleName
Update IBM_DBHEALTH.dbo.LoginReport
Set DBRole =@DBNewRole
Where [UserName] = @UserName
and DBName =@DBName
and [SQLInstanceName] = @SQLInstanceName
and DBRole =@DBCurrentRole
End
End
Else -- To get the regular Login/User info
Begin
select @DateCreated = createdate from sys.syslogins where name =@LoginName
Set @UserStatus = 'Active'
select @UserStatus = 'InActive' from sys.syslogins where name =@LoginName and (status = 10 or denylogin =1)
Select DBRole from LoginReport where LoginName =@LoginName and DBName =@DBName
If (@@ROWCOUNT =0)
Begin
Insert into IBM_DBHEALTH.dbo.LoginReport
(ServerName,[SQLInstanceName],[DBType],DBName,LoginName,UserName,[UserStatus],DBRole,DateCreated)
values(@ServerName,@SQLInstanceName,@DBType,@DBName,@LoginName,@UserName,@UserStatus,@RoleName,@DateCreated)
End
Else
Begin
Select @DBCurrentRole = DBRole from LoginReport
where [LoginName] = @LoginName
and DBName =@DBName
Set @DBNewRole = @DBCurrentRole +'\'+@RoleName
Update IBM_DBHEALTH.dbo.LoginReport
Set DBRole =@DBNewRole
Where [LoginName] = @LoginName
and DBName =@DBName
and [SQLInstanceName] = @SQLInstanceName
and DBRole =@DBCurrentRole
End
End
Fetch next from GRPName_CUR into @UserName,@RoleName, @LoginName
End
Close GRPName_CUR
Deallocate GRPName_CUR
Truncate table #DBUser_08
Fetch Next From DBUser_Cur into @DBName
End
Close DBUser_Cur
Deallocate DBUs