Member Avatar for clflyer

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.

  • 3 Contributors
  • 3 Replies
  • 189 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by bluehangook629

Dani AI

Generated

Short diagnosis and cause: the error is usually not a bug in OLEDB but a SQL context / syntax problem — T‑SQL expects a securable or a current‑database grant, and your command must run in the States_02 database (or use the database securable form). You can grant SELECT at the database level, but the statement must be valid and the principal must exist as a database user. (learn.microsoft.com)

Recommended (clean, maintainable) approach — map the login to a database user and add that user to the built‑in read role:

USE [States_02];

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'web')
  CREATE USER [web] FOR LOGIN [web];

-- preferred on SQL Server 2012+:
ALTER ROLE [db_datareader] ADD MEMBER [web];
-- older servers can use:
-- EXEC sp_addrolemember 'db_datareader','web';

Create‑user and role membership are the standard pattern. Use ALTER ROLE on modern servers (sp_addrolemember is deprecated). (learn.microsoft.com)

If you really want an explicit GRANT, either run it while connected to the database or use the DATABASE securable form:

USE [States_02];
GRANT SELECT ON DATABASE::[States_02] TO [web];

Or run GRANT SELECT TO [web] while already in States_02. If your VB/OLEDB connection defaults to master, the engine will not find the object — set the connection’s Initial Catalog/Database to States_02 or issue USE [States_02] before the GRANT. (learn.microsoft.com)

Quick troubleshooting queries and permission notes:

SELECT name FROM sys.databases WHERE name = 'States_02';
SELECT name, type_desc FROM sys.server_principals WHERE name = 'web';

USE [States_02];
SELECT name, type_desc FROM sys.database_principals WHERE name = 'web';

Also note ALTER ROLE (and changing fixed role membership) requires appropriate rights — in some cases membership in db_owner is required. If you can grant manually in SSMS but fail from code, compare the connection string user and Initial Catalog. (learn.microsoft.com)

Contextual notes tied to the thread: was partly right that you can’t naively grant “on entire database” without the correct syntax/context; using a database user + db_datareader or the documented GRANT forms is the robust fix for ’s scenario.

Recommended Answers

All 3 Replies

Member Avatar for bluehangook629

I dont think you are allowed to grant access to a user on entire database.
What is the error message?

Member Avatar for Begginnerdev

Are you using any DBMS?

Member Avatar for bluehangook629

Are you using any DBMS?

I see that he is using OLEDB, and I don't think there is one.
I may be wrong...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.