[Question] ODBC Read/Write Permission - Can Create a Stored Procedure?

Question

I am not an SQL/Database Expert, but I would like to know more and am curious if a user with an ODBC Read/Write Access to the database. Can it create/alter stored procedures?

Thanks a lot!

Answer 1

The data access component like ODBC don't matter for this case.

The logged on user need DDL permissions, then he can create any kind of SQL object, like stored procedures, functions, view etc.

See for example "db_ddladmin" database role at https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

Answer 2

Hi @homerm,

The easiest way is to add a user to the db_ddladmin role.

-- Older method EXEC sp_addrolemember 'db_ddladmin','username';

-- New method ALTER ROLE db_ddladmin ADD MEMBER username;

ALTER ROLE (Transact-SQL)

This will give the user the ability to create/modify/drop any object in any schema.

Not clear about your meaning of ODBC Read/Write Permission, we need further explain for your question about the ODBC driver.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Do not add users directly to db_ddladmin. Instead do this:

CREATE ROLE our_ddladmin
DENY ALTER ANY DATABASE DDL TRIGGER to db_ddladmin
ALTER ROLE db_ddladmin ADD MEMBER our_ddladmin

And then add users to the role our_ddladmin.

The reason for this extra role is that db_ddladmin permits people to add/modify DDL triggers, but that should not be a developer activity.

Answer 4

The ODBC account has read/write permission. Can it create a stored procedure in the database or not?

The problem with this question is that we don't really know what you are talking about. What "ODBC account"? There is no such thing as an "ODBC account" in the SQL Server world. Maybe you have an application that uses ODBC and that uses a login hardcoded into the application, and this is the account you are talking about, but we can only guess.

And when you say read/write permission, we don't know exactly which permissions you have granted. The interpretation that is closest at hand is that the account you are talking about have SELECT, INSERT, DELETE, and UPDATE permissions, alternatively membership in the fixed roles db_datareader and db_datawriter. And if this is the case, the account has no right to create stored procedures. But we don't know all details. Also, the account could be member of a role that has permissions to create procedures.

Anyway, you can find out yourself this way:

EXECUTE AS LOGIN = 'the ODBC account'
go
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')
go
REVERT

That is, you impersonate the ODBC account, and then you request to see which permissions you have on database level as the ODBC account. If you see CREATE PROCEDURE in the list, the account has permission to do exactly that.