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');


  • Are there any other roles (server level and database level) that the Logins and associated Users are in? May 2, 2016 at 19:49
  • unclefredo figured it out...CREATE PROCEDURE was denied on public for some very odd reason that I did not check
    – IanG
    May 2, 2016 at 20:03

3 Answers 3


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?

  • I know it was you unclefredo and you didn't break my heart! Yes, you nailed it, Create Procedure was Denied on public. Thank you so much! I have no idea why on earth the former DBA would do that. Turns out it was on two databases, the second database happened to be the one that I tested as my "control" so I thought it was instance-wide. It was just on 2 DBs. Several learning's here...don't just search the roles/permissions for the specific user! Thanks again!
    – IanG
    May 2, 2016 at 20:02
  • Always good when a lunchtime SO answer pays off. Glad to have helped. To be honest, I've seen DBAs do it on model so that every new database has this as a default. Must've been paranoid about the creation of stored procedures - that's my only guess. Good luck with the thankless task of implementing "least privilege" - a noble quest indeed..
    – unclefredo
    May 3, 2016 at 11:15

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.

  • I see 1 Protect Type of GRANT for the action CONNECT
    – IanG
    Apr 26, 2016 at 13:49
  • 1
    Thanks Cody, turns out you were very close...like you I was concentrating on the specific user, unclefredo expanded it to all users thinking it might be a problem with public which it was...create procedure was denied on public. Thanks again.
    – IanG
    May 2, 2016 at 20:09
  • Could not find stored procedure 'sp_helpprotect'.
    – Nick
    Jan 12, 2018 at 10:43

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:

Create Procedure spMyProc
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.

  • Thanks for the effort...unclefredo answered it. I was pretty careful to make sure this was not the case as I have seen this behavior also.
    – IanG
    May 2, 2016 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.