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
      
---------------------------------------------------------------------------------------------------------------------------------------------
SandeepiiiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SandeepiiiAuthor Commented:
Thanks for your comments but the link pasted (It converts some functions only) ,Does not help as i would like to have db users and logins information captured  into a table for our internal purpose.
Thanks .
SandeepiiiAuthor Commented:
Hi Experts could it possible to get the information of DB role information of all database except for temp and model databases excluded.Thanks in advance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.