Sql-server – SQL Logins in db_owner role on database cannot create stored procedures

permissionssql serversql server 2014stored-procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the account the db_owner role in the database. The developer reports that he can no longer create stored procedures.

I have validated this is true in this particular SQL 2014 Instance, I cannot reproduce it anywhere else. I have done about everything I can think of to fix this. The best clue I have is that I created a new SQL login, and gave it db_owner in the same database. It also cannot create a procedure. The original login cannot create procedures in other user databases even though it has db_owner.

Attempting to create a procedure gives:

Msg 262, Level 14, State 18, Procedure TheProcedure, Line 30
CREATE PROCEDURE permission denied in database 'DBTest'.

The error message is correct, CREATE PROCEDURE does not show up for the user, even after I explicitly assign it. The user can Alter any procedure. UDF creation gets a similar error:

CREATE FUNCTION permission denied in database…

Anyone have an idea of what could be happening?

When I do a:

select * from fn_my_permissions(null, 'DATABASE')

CREATE PROCEDURE is not listed. I have checked for any DENY in sys.permissions, there is none.

The following query:

SELECT * FROM fn_my_permissions('xyz', 'USER');

returns:

IMPERSONATE, VIEW DEFINITION, ALTER, CONTROL

Best Answer

Interesting one - difficult to pin this one down. Have you thought about looking at the public role?

sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s'

Does that bring you back anything?

Related Question