Question Create Database, Grant Select

Clflyer

New member
Joined
Jan 13, 2015
Messages
2
Location
Sugar Grove, IL
Programming Experience
10+
I have a problem.
I need to create a database monthly using a program. The database is States_XX where XX is the number for next month.
Then I need to GRANT Select to that database.

I am connected to the SQL Server.
Creating the database was easy.
Applying the GRANT SELECT is not.

Here is the Code I'm using:
'Create the new States_XX database
SQL = "CREATE DATABASE " & DbName & ";"
Ok = SQLCommand(SQL, Cmd, Conn) This Works
If Ok Then
SQL = "GRANT SELECT, UPDATE, INSERT, DELETE,ALTER ON [dbo].[" & DbName & "] TO [DbBuilder];"
Ok = SQLCommand(SQL, Cmd, Conn) This Fails
End If

Function SQLCommand(ByVal SQL As String, ByVal cmd As OleDbCommand, ByVal conn As OleDbConnection) As Boolean
'Set the commandtext and execute the command
cmd.CommandText = SQL
Try
cmd.ExecuteNonQuery()
Return = True
Catch ex As Exception
Me.lblStatus.Text = "Error executing " & SQL & vbCrLf & ex.Message
Me.lblStatus.ForeColor = Color.Red
conn.Close()
Return False
End Try

End Function

This is the error I get back from the function SQLCommand:
Error executing GRANT SELECT ON [dbo].[States_02] TO [WebpageLogin];
Cannot find the object 'States_02', because it does not exist or you do not have permission.


The database has been created, and DbBuilder is the owner, DbBuilder is who I logged into the Server as. The database has no permissions.
How do I Grant permissions to the database?

TIA,
Bill :confused:
 
Back
Top