I have a program that I wrote that creates a database in SQL Server called States_02 as a sysadmin user.
Later on I want to grant SELECT permissions to that database to another user, web
I always get an error that it cannot find States_02 or I don't have permissions to do it.
My Code:
Dim Conn As New OleDbConnection(ConnStr)
Try
Conn.Open()
Catch ex As Exception
Me.lblStatus.Text = "Unable to connect to Server." & vbCrLf & ex.Message
Me.lblStatus.ForeColor = Color.Red
Exit Sub
End Try
Dim DbName As String = Me.txtStates.Text
Dim SQL As String = "GRANT SELECT ON " & DbName & " TO web"
Dim Cmd As New OleDbCommand(Sql, Conn)
Try
Cmd.ExecuteNonQuery()
Catch ex As Exception
Me.lblStatus.Text = "Error unable to " & SQL & vbCrLf & ex.Message
Me.lblStatus.ForeColor = Color.Red
End Try
Conn.Close()
The user has public and sysadmin server roles and is the same user who created the database. This user can also login to SQL Server and grant the permissions manually. I'm a little puzzled on this one.
