3

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
  • Are there any other roles (server level and database level) that the Logins and associated Users are in? – Solomon Rutzky May 2 '16 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 '16 at 20:03
2
+50

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 '16 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 '16 at 11:15
1

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 '16 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 '16 at 20:09
  • Could not find stored procedure 'sp_helpprotect'. – Nick Jan 12 '18 at 10:43
0

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.

  • 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 '16 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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