SQL Logins in db_owner role on database cannot create stored procedures

Question

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

Answer 1

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?

Answer 2

Unlike sysadmin which bypasses checks, the built in database roles aren't so special that they can't be overridden with a DENY.

Try looking at Exec sp_helpprotect Null, 'Username' and seeing what DENY records show up.

Answer 3

Short Answer:

• Try changing the default database of the dev user to their particular database.

• In the Query window within SS Studio, make sure they're using the right database context within the Query window via the database drop-down list or T-SQL USE [DBNAME].

Long Answer:

I cannot speak for every circumstance but I know a case where this definitely happened to us. The default database was set to 'master' for our particular user and the had SA rights. We removed SA rights and behold, they suddenly could not create the procedures they needed to--only CONNECT.

Changing the user's default database helped to their database correct this issue. (It turns out they only had CONNECT rights to the master database) Additionally, the USE statement worked in the SS STudio Query window:

USE DBTest
go
Create Procedure spMyProc
as
select @@servername

Having said this, I'd check the master database (and other databases) to see if the developers have been erroneously putting their code in other databases instead of the proper database--when they had SA. For us removing SA rights definitely revealed a glowing issue with how the developers were using the system.

p.s. Let me know if they're using partially contained databases as there can be identically named users both inside and outside the database that would cause similar, seemingly, permissions based issues.