{"id":5703,"date":"2022-08-30T03:56:00","date_gmt":"2022-08-29T22:26:00","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/"},"modified":"2022-08-30T03:56:00","modified_gmt":"2022-08-29T22:26:00","slug":"solved-get-the-users-associated-with-database","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/","title":{"rendered":"[Solved] Get the users associated with Database"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n
function SQL-Get-Logins\n{\n    <#\n    .SYNOPSIS\n    Returns a list of SQL Server logins defined on the specified server\/instance.\n\n    .DESCRIPTION\n    This function returns a complete list of all logins defined for the specified\n    server\/instance, including the login type, the default database, and the server\n    role.\n\n    .PARAMETER server\n    The computer hosting SQL Server.\n\n    .PARAMETER instance\n    The instance to be used on that server.\n\n    .PARAMETER saPassword\n    The 'sa' password on that server\\instance\n\n    .EXAMPLE\n    SQL-Get-Logins myserver myinstance sa | %{ Write-Host $_.LoginName }\n    #>\n\n    param (\n        [parameter(Mandatory = $true)][string] $server,\n        [parameter(Mandatory = $false)][string] $instance=\"MSSQL\",\n        [parameter(Mandatory = $true)][string] $saPassword\n    )\n\n    $logins = $null\n\n    try\n    {\n        $query = @\"\n        select a.name as LoginName, a.type_desc as LoginType, a.default_database_name as DefaultDBName,\n        case when b.sysadmin = 1 THEN 'sysadmin'\n                  when b.securityadmin=1 THEN 'securityadmin'\n                  when b.serveradmin=1 THEN 'serveradmin'\n                  when b.setupadmin=1 THEN 'setupadmin'\n                  when b.processadmin=1 THEN 'processadmin'\n                  when b.diskadmin=1 THEN 'diskadmin'\n                  when b.dbcreator=1 THEN 'dbcreator'\n                  when b.bulkadmin=1 THEN 'bulkadmin'\n                  else 'Public' end as ServerRole\n        from sys.server_principals a join master..syslogins b on a.sid=b.sid where a.type  <> 'R' and a.name not like '##%'\n\"@\n\n        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection\n        $sqlConnection.ConnectionString = \"Server=$server\\$instance;Database=master;User Id=sa;Password=$saPassword;Trusted_Connection=False;\"\n        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query, $sqlConnection)\n        $dataSet = New-Object System.Data.Dataset\n        $adapter.Fill($dataSet) | Out-Null \n\n        $logins = $dataSet.Tables[0]\n\n        foreach ($login in $logins)\n        {\n            $loginName = $login.ItemArray[0]\n            $loginType = $login.ItemArray[1]\n            $defaultDbName = $login.ItemArray[2]\n            $serverRole = $login.ItemArray[3]\n\n            Write-Output \"$($MyInvocation.InvocationName): LoginName: $loginName, LoginType: $loginType, Default database: $defaultDbName, Server Role: $serverRole\"\n        }\n    }\n\n    catch\n    {\n        Write-Error \"$($MyInvocation.InvocationName): $_\"\n    }\n\n    return $logins\n}\n<\/code><\/pre>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            1<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved Get the users associated with Database <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] function SQL-Get-Logins { <# .SYNOPSIS Returns a list of SQL Server logins defined on the specified server\/instance. .DESCRIPTION This function returns a complete list of all logins defined for the specified server\/instance, including the login type, the default database, and the server role. .PARAMETER server The computer hosting SQL Server. .PARAMETER instance The instance … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[1153],"class_list":["post-5703","post","type-post","status-publish","format-standard","hentry","category-solved","tag-powershell"],"yoast_head":"\n[Solved] Get the users associated with Database - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Get the users associated with Database - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] function SQL-Get-Logins { <# .SYNOPSIS Returns a list of SQL Server logins defined on the specified server\/instance. .DESCRIPTION This function returns a complete list of all logins defined for the specified server\/instance, including the login type, the default database, and the server role. .PARAMETER server The computer hosting SQL Server. .PARAMETER instance The instance ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-08-29T22:26:00+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Get the users associated with Database\",\"datePublished\":\"2022-08-29T22:26:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/\"},\"wordCount\":16,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"powershell\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/\",\"name\":\"[Solved] Get the users associated with Database - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-08-29T22:26:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-get-the-users-associated-with-database\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Get the users associated with Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Get the users associated with Database - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Get the users associated with Database - JassWeb","og_description":"[ad_1] function SQL-Get-Logins { <# .SYNOPSIS Returns a list of SQL Server logins defined on the specified server\/instance. .DESCRIPTION This function returns a complete list of all logins defined for the specified server\/instance, including the login type, the default database, and the server role. .PARAMETER server The computer hosting SQL Server. .PARAMETER instance The instance ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/","og_site_name":"JassWeb","article_published_time":"2022-08-29T22:26:00+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Get the users associated with Database","datePublished":"2022-08-29T22:26:00+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/"},"wordCount":16,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["powershell"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/","url":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/","name":"[Solved] Get the users associated with Database - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-08-29T22:26:00+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-get-the-users-associated-with-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Get the users associated with Database"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/5703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=5703"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/5703\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=5703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=5703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=5703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}