SQL Server 2008 Powershell

Hi experts I would like to ask help on writing Powershell script for below.

First I would like to Select the Database.                                                                                                                           Second Create a Temp Table                                                                                                                                        Third I would like to Get the Two tables like this @DBName+'.dbo.sp_helpuser'                                                 Fourth Then insert the data in the Temperary table

set @ dbname =Select   name from master.dbo.sysdatabases
                where Name not in ('tempdb','Pubs','Northwind','Model')
 
            -- get DB info
 
                If (Select isnull(object_id('tempdb..#DBUser'), 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
                       
                Declare @DBQuery varchar(200)
               
                Set @DBQuery = @DBName+'.dbo.sp_helpuser'

                  Insert into #DBUser exec (@DBQuery)

  • I am lost. Could you expand a bit on what  is your ultimate goal, before dwelling into the details please?

  • Hi Thanks for your reply.I need to take To Collect SQL Login and User access report for all user databases on a SQL server.
    Iam getting all user database from this query:Select   name from master.dbo.sysdatabases
                    where Name not in ('tempdb','Pubs','Northwind','Model')

    Then iam combining with SP_helpuser .
    How to do this in powershell script .? As sysdatabases will help me to loop through the user database and sp_helpuser will give me the user information.Then i will combine the two output into a temp table table.
    Iam trying to do in powershell scripts i already have a T sql code.Thanks in advance. 
  • Why do you want to re-create the report in powershell if you already have the code in t-sql? You can connect from powershell to the sql, run the query/procedure and just consume the results. Then you can do what ever you want with them. Serialize them to csv, print it as html report. 

  • Hey sandeepiii,

    I find that converting real-world SQL scripts is a fun way to learn PowerShell and SMO. 

    Here is an approximation of what you're looking for. You can modify as you wish. This creates a custom object with most of the information that your original script contained. I didn't know what DbType referred to, however.

    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server "sql2005express\sqlexpress"
    $servername = $server.netname
    $sqlinstancename = $server.DomainInstanceName
    $dbs = $server.databases | Where-Object { 'tempdb','Pubs','Northwind','model' -notcontains $_.Name }
    $loginreport = @()

    foreach ($db in $dbs) {
    $users = $db.users | Where-Object { 'guest','sys','INFORMATION_SCHEMA'-notcontains $_.Name -and $_.Name -notlike '*##*' -and $_.Name -notlike '*$*' -and $_.Name -ne $null }

    foreach ($user in $users) {
    $username = $user.name
    $userid = $user.id
    $roles = $user.EnumRoles() -join ","
    $defaultschema = $user.Properties["DefaultSchema"].Value
    $sid = "0x"; $user.sid | ForEach-Object { $sid += ("{0:X}" -f $_).PadLeft(2, "0") }
    $login = $server.logins["$($user.login)"]
    $loginname = $login.name

    if ($login -eq $null) {
    $userstatus = "InActive"
    $defaultdb = $null
    $createdate = $user.Properties["CreateDate"].Value
    } else {
    $defaultdb = $login.Properties["DefaultDatabase"].Value
    $createdate = $login.Properties["CreateDate"].Value

    if ($login.hasAccess -eq $false -or $login.IsDisabled -eq $true) {
    $userstatus = "InActive"
    } else { $userstatus = "Active" }
    }


    $loginreport += [PSCustomObject]@{
    ServerName = $servername
    SqlInstanceName = $sqlinstancename
    DatabaseName = $db.name
    UserName = $username
    UserId = $userid
    Roles = $roles
    DefaultSchema = $defaultschema
    SID = $SID
    Login = $loginname
    UserStatus = $userstatus
    DefaultDB = $defaultdb
    CreateDate = $createdate
    }
    }
    }

    $loginreport

    This took 486 milliseconds to return 57 results!

    You can also find the code on gist.