Modules/SharePointDsc.Search/SPSearchServiceApp.psm1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
function Confirm-UserIsDBOwner
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLServer,

        [Parameter(Mandatory = $true)]
        [System.String]
        $Database,

        [Parameter(Mandatory = $true)]
        [System.String]
        $User,

        [Parameter()]
        [PSCredential]
        $DatabaseCredentials
    )

    $connection = New-Object -TypeName "System.Data.SqlClient.SqlConnection"
    $command = New-Object -TypeName "System.Data.SqlClient.SqlCommand"

    # If we specified SQL credentials then try to use them
    if ($PSBoundParameters.ContainsKey("DatabaseCredentials") -and `
            [System.String]::IsNullOrEmpty($DatabaseCredentials) -eq $false)
    {
        $marshal = [Runtime.InteropServices.Marshal]
        $dbCredentialsPlainPassword = $marshal::PtrToStringAuto($marshal::SecureStringToBSTR($DatabaseCredentials.Password))
        $connection.ConnectionString = "Server=$SQLServer;Integrated Security=False;User ID=$($DatabaseCredentials.Username);Password=$dbCredentialsPlainPassword;Database=master"
        $checkUser = $DatabaseCredentials.UserName
    }
    else # Just use Windows integrated auth
    {
        $connection.ConnectionString = "Server=$SQLServer;Integrated Security=SSPI;Database=master"
        $checkUser = $User
    }

    try
    {
        $connection.Open()
        $command.Connection = $connection

        $command.CommandText = @"
USE [{0}]
 
SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
 FROM sys.database_role_members AS DRM
 RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R' AND DP2.name = '{1}' AND DP1.name = 'db_owner'
"@
 -f $Database, $checkUser

        $result = ($command.ExecuteScalar() -eq "db_owner")
    }
    catch
    {
        throw "Error while running SQL query: $($_.Exception.InnerException.Message)"
        $result = $false
    }
    finally
    {
        if ($connection.State -eq "Open")
        {
            $connection.Close()
            $connection.Dispose()
        }
    }

    return $result
}

function Set-UserAsDBOwner
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.String]
        $SQLServer,

        [Parameter(Mandatory = $true)]
        [System.String]
        $Database,

        [Parameter(Mandatory = $true)]
        [System.String]
        $User,

        [Parameter()]
        [PSCredential]
        $DatabaseCredentials
    )

    $connection = New-Object -TypeName "System.Data.SqlClient.SqlConnection"
    $command = New-Object -TypeName "System.Data.SqlClient.SqlCommand"

    # If we specified SQL credentials then try to use them
    $sqlauth = $false
    if ($PSBoundParameters.ContainsKey("DatabaseCredentials") -and `
            [System.String]::IsNullOrEmpty($DatabaseCredentials) -eq $false)
    {
        $sqlauth = $true
        $marshal = [Runtime.InteropServices.Marshal]
        $dbCredentialsPlainPassword = $marshal::PtrToStringAuto($marshal::SecureStringToBSTR($DatabaseCredentials.Password))
        $connection.ConnectionString = "Server=$SQLServer;Integrated Security=False;User ID=$($DatabaseCredentials.Username);Password=$dbCredentialsPlainPassword;Database=master"
    }
    else # Just use Windows integrated auth
    {
        $connection.ConnectionString = "Server=$SQLServer;Integrated Security=SSPI;Database=master"
    }

    try
    {
        $connection.Open()
        $command.Connection = $connection

        $sql = @"
USE [{0}]
 
DECLARE @NewUserName sysname;
 
SET @NewUserName = '{1}';
 
/* Users are typically mapped to logins, as OP's question implies,
so make sure an appropriate login exists. */
IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = @NewUserName) BEGIN
    /* Syntax for SQL server login. See BOL for domain logins, etc. */
    DECLARE @LoginSQL as varchar(500);
    SET @LoginSQL = 'CREATE LOGIN ['+ @NewUserName + '] {2}';
    EXEC (@LoginSQL);
END
 
/* Create the user for the specified login. */
IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = @NewUserName) BEGIN
    DECLARE @UserSQL as varchar(500);
    SET @UserSQL = 'CREATE USER [' + @NewUserName + '] FOR LOGIN [' + @NewUserName + ']';
    EXEC (@UserSQL);
END
 
IF NOT EXISTS
    (SELECT DP1.name AS DatabaseRoleName,
       isnull (DP2.name, 'No members') AS DatabaseUserName
     FROM sys.database_role_members AS DRM
     RIGHT OUTER JOIN sys.database_principals AS DP1
       ON DRM.role_principal_id = DP1.principal_id
     LEFT OUTER JOIN sys.database_principals AS DP2
       ON DRM.member_principal_id = DP2.principal_id
    WHERE DP1.type = 'R' AND DP2.name = @NewUserName AND DP1.name = 'db_owner')
BEGIN
    DECLARE @roleSQL as varchar(500);
    SET @roleSQL = 'ALTER ROLE db_owner ADD MEMBER [' + @NewUserName + ']';
    EXEC (@roleSQL);
END
"@


        if ($sqlauth -eq $true)
        {
            # Using SQL Auth
            $options = "WITH PASSWORD = ''{0}'', CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF" -f $DatabaseCredentials.GetNetworkCredential().Password
            $checkUser = $DatabaseCredentials.UserName
        }
        else
        {
            # Using Windows Integrated Auth
            $options = "FROM WINDOWS"
            $checkUser = $User
        }

        $command.CommandText = $sql -f $Database, $checkUser, $options
        $null = $command.ExecuteNonQuery()
    }
    catch
    {
        throw "Error while running SQL query: $($_.Exception.InnerException.Message)"
    }
    finally
    {
        if ($connection.State -eq "Open")
        {
            $connection.Close()
            $connection.Dispose()
        }
    }
}

Export-ModuleMember -Function *