SQL Server 2012 Administration Powershell

Hi Experts,
I have a existing scripts, Could you please help me in converting this existing script to Powershell,
Thanks in advance.


USE [DBHEALTH]
GO
/****** Object:  StoredProcedure [dbo].[  _MSSAuditReport_test]    Script Date: 05/27/2015 07:08:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[  AOD_MSS_AuditReport_test]
as      
--*******************************************************************************************                      
--Stored Proc        :   _MSSQL_AuditReport                    
--Desc                    : To Collect SQL Login and User access report for all databases on a SQL server.

--******************************************************************************************
Declare @ServerName varchar(30)
Declare @RoleName Varchar(1000)
Declare @LoginName varchar(500)
Declare @UserName varchar(500)
Declare @ServerRole varchar (1000)
Declare @MemberName Varchar (500)
Declare @SQLInstanceName varchar (1000)
Declare @UserStatus varchar (100)
Declare @DBType varchar (100)
Declare @LoginName_DBUser varchar (500)
Declare @DBCurrentRole Varchar (1000)
Declare @CurrentSRVRole Varchar (1000)
Declare @NewSRVRole Varchar (1000)
Declare @DBNewRole Varchar (1000)
Declare @DBName varchar(1000)
Declare @DateCreated datetime
Declare @NodeName1 varchar (1000)
Declare @NodeName2 varchar (1000)
Declare @NodeName varchar (1000)    
--Below iam writing to get all instances in the network so that i can capture the data of all instances in network
Select @SQLInstanceName = srvname from sys.sysservers;
Set @DBType ='SQLServer'

SET NOCOUNT ON
If (Select isnull(object_id('  _DBHEALTH..LoginReport'), 0)) = 0

CREATE TABLE [dbo].[LoginReport](
      [ServerName] [varchar](1000) NULL,
      [SQLInstanceName] [varchar](1000) NULL,
      [DBType] [varchar](500) NULL,
      [DBName] [varchar](500) NULL,
      [LoginName] [varchar](500) NULL,
      [UserName] [varchar](500) NULL,
      [UserStatus] [varchar](500) NULL,
      [ServerRole] [varchar](1000) NULL,
      [DBRole] [varchar](1000) NULL,
      [DateCreated] [datetime] NULL
) ON [PRIMARY]

Truncate table [LoginReport]
--------------To get the SQL Cluster Node Names -------------------

                  SELECT NodeName FROM sys.dm_os_cluster_nodes
                  If @@ROWCOUNT = 0
                        begin
                              Select @ServerName = @@ServerName
                        End
                  Else
                   Begin
                              If (Select isnull(object_id('tempdb..#CLNodeName'), 0)) = 0
                                    Create table #CLNodeName (NodeName varchar(500) null)
                                    Truncate table #CLNodeName

                                Insert into #CLNodeName SELECT NodeName FROM sys.dm_os_cluster_nodes

                              Declare NodeName_CUR cursor for
                                    Select NodeName from #CLNodeName
                                    For read only
                              
                              Open NodeName_CUR
                              Fetch Next from NodeName_CUR into @NodeName
                        
                              While @@fetch_status = 0
                              Begin
                                    Set @NodeName1 =@NodeName
                                    Set @ServerName = @NodeName2 +' \ '+ @NodeName1                              
                                    
                                    Fetch Next from NodeName_CUR into @NodeName
                                    set @NodeName2 = @NodeName1
                              end
                              Close NodeName_CUR                  
                              Deallocate NodeName_CUR
                        End

      
-----------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   _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   _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   _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   _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 DBUser_Cur


-----------------------------------------------------------------------------------------------------------------------------------------------
-- To collect Server Role for all Logins
                  if (select isnull(object_id('##LoginServerRole'), 0)) = 0
                  create table  #LoginServerRole
                                    (ServerRole varchar(25),
                                     MemberName varchar(128),
                                     MemberSidId varbinary(85))
                  truncate table #LoginServerRole
                  
                  Select @DBQuery = @SQLInstanceName + '.master.dbo.sp_helpsrvrolemember'
                  insert into #LoginServerRole exec (@DBQuery)

                  Declare LoginSrvRole_Cur cursor for                        
                        Select       ServerRole, MemberName from #LoginServerRole where MemberName not like '%$%'
                        For read only

                  Open LoginSrvRole_Cur
                  Fetch next from LoginSrvRole_Cur into @ServerRole,@MemberName
                  While @@fetch_status = 0
                  Begin
                        select @DateCreated = createdate from sys.syslogins where name =@MemberName
                        Set @UserStatus = 'Active'
                        select @UserStatus = 'InActive' from sys.syslogins where name =@MemberName and (status = 10 or denylogin =1)
                        
                        Select ServerRole from LoginReport where LoginName =@MemberName
                        If (@@ROWCOUNT =0)
                              Begin
                                    Insert into LoginReport
                                       (ServerName,[SQLInstanceName],[DBType],DBName,LoginName,[UserStatus],ServerRole,DateCreated)
                                    values (@ServerName,@SQLInstanceName,@DBType,'MSDB',@MemberName,@UserStatus, @ServerRole, @DateCreated)
                              End
                        Else
                              Begin
                                    Select @CurrentSRVRole = ServerRole from LoginReport
                                          where [LoginName] = @MemberName
                                    If (@CurrentSRVRole =' ' or @CurrentSRVRole is NULL)
                                          Begin
                                          Set @NewSRVRole = @ServerRole
                                          end
                                    else
                                          begin
                                          Set @NewSRVRole = @CurrentSRVRole +'\'+@ServerRole
                                          end
                                    print @MemberName                              
                                    print @NewSRVRole
                                    
                                    Update   _DBHEALTH.dbo.LoginReport
                                                Set ServerRole =@NewSRVRole
                                                Where [LoginName] = @MemberName
                                                and [SQLInstanceName] = @SQLInstanceName

                              End

                      Fetch next from LoginSrvRole_Cur into @ServerRole,@MemberName
                  End
                  
                  Close LoginSrvRole_Cur
                  Deallocate LoginSrvRole_Cur
      
---------------------------------------------------------------------------------------------------------------------------------------------