Granting Explicit View Definition Permissions on Stored Procedure to dbo

Question

Granting Explicit View Definition Permissions on Stored Procedure to dbo

charlesd

Default port

Points: 1427
More actions
March 6, 2013 at 2:48 pm

#290394

The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16201 More actions · Answer 1

You could grant view definition rights in the test and production environments to the developers for the dbo schema. This way they would not need to grant view definition to individual objects but would already have it as the objects are moved between environments.

GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [developers]

GO

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?

What JeremyE showed will allow developers to view the definition of all the objects in the dbo schema which may be the simplest way to go about this, or it may be too many permissions, that you'll need to decide. If you want your developers to package a GRANT with their stored proc definition when they ship it from DEV to TEST and eventually PROD then they can provide something like this:

GRANT VIEW DEFINITION ON OBJECT::[ObjectSchemaName].[ObjectName] TO [DeveloperUserName];

This assumes the developer already has a Database User in the TEST and PROD databases. If they do not, the GRANT will fail.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

charlesd Default port Points: 1427 More actions · Answer 3

Thanks for the input. It does look like granting permissions at the schema level is the best option. In most cases, this will probably work globally for all objects in the schema. However, it is good to know that there is an option for granting permissions at the object level by specifying the schema as well. I will test that out to make sure the permission "sticks" for the individual developer when specifying it with the schema name.

charlesd Default port Points: 1427 More actions · Answer 4

Unfortunately, a user is still unable to grant explicit view definition permissions to themselves using this method, so there is no apparent solution other than globally granting permissions on the entire schema.

Does anyone else know of a method allowing a user to grant explicit view definition permissions to themselves on a specific object?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

charlesd (3/7/2013)
Unfortunately, a user is still unable to grant explicit view definition permissions to themselves using this method, so there is no apparent solution other than globally granting permissions on the entire schema.
Does anyone else know of a method allowing a user to grant explicit view definition permissions to themselves on a specific object?

No, if anyone could grant themselves anything that would kind of violate the idea of having permissions 😀

The assumption was that a DBA with db_owner membership in the database was deploying the developer's scripts. Typical sequence is:

1. Developer develops object in dev.

2. Developer scripts out object and any permissions.

3. DBA deploys script in TEST or PROD.

If the script contained a GRANT for VIEW DEFINITION then the developer would gain the permission when the DBA deployed the script to the next environment.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

charlesd Default port Points: 1427 More actions · Answer 6

That is the way it occurs except that in this case the dba scripts out the stored procedures and permissions. The problem is that there is no view definition permission for the developer who created the stored procedure, since they can't grant it to themselves. Therefore, the view definition permission is not available for the developer when the stored procedure is moved to Test and Production. The desire is for the view definition permission to be scripted out with the stored procedure when it is moved.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

OK, I see where the disconnect is coming in. I like it when the developers send their own scripts, then there is no mistaking what they wanted to go to the next environment. In your case not getting the permissions when the object is scripted is a function of how the DBA has SSMS configured. Under Tools > Options SQL Server Object Explorer > Scripting you can set the options to script out permissions:

If you are granting at the object level as I showed then the GRANT will be scripted out from SSMS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

charlesd Default port Points: 1427 More actions · Answer 8

I think you are missing the point. I do script out object level permissions with the Create\Alter script. However the view definition permission does not exist for the developer who created the stored procedure. I am trying to determine how the developer can explicitly assign this permission to himself so it will script out with the Create\Alter script and any other users' permissions.

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

charlesd (3/7/2013)
I think you are missing the point. I do script out object level permissions with the Create\Alter script. However the view definition permission does not exist for the developer who created the stored procedure. I am trying to determine how the developer can explicitly assign this permission to himself so it will script out with the Create\Alter script and any other users' permissions.
Thanks

When you were saying 'grant to themselves' before I was assuming you meant in TEST or PROD. Maybe I am assuming too much. If they have permissions to create a stored proc in DEV then they should have the ability to grant VIEW DEFINITION permissions on the object, and once they grant themselves this permission [at the object level], then when the DBA scripts out the object they will get the GRANT as long as they have configured their SSMS scripting options.

1. Developer develops object in dev.

2. Developer grants themselves VIEW DEFINITION on the object in DEV.

3. DBA (with a properly configured SSMS) scripts out the object and any permissions will go with it.

4. DBA deploys script in TEST or PROD.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323518 More actions · Answer 10

maybe i'm reading the requirement differently;

it sounds like the Developer has db_ddladmin.

if the developer creates a new object, he of course has access to it, because he was the creator, but no explicit permission gets created...it's inferred due to the role.

so the request is to generate an inferred permission i think, correct?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

charlesd Default port Points: 1427 More actions · Answer 11

Yes, that is essentially correct. The developers have dbo rights in the development databases, which implicitly gives them all necessary rights on stored procedures i.e., execute, view definition, etc.

However a user, even one with dbo rights, cannot explicitly grant permissions to themselves. So when the stored procedure is scripted out from development, it only contains the explicitly granted permissions and not the implicit ones. Developers only have datareader rights in the Test and Production databases, so they are unable to view the definition of a stored procedure in those environments to determine if it is identical to the one in production.

I am just trying to determine if there is any way a developer can grant himself view definition permission on a stored procedure he has created in development so that permission can be carried forward to Test and Production.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

charlesd (3/7/2013)
Yes, that is essentially correct. The developers have dbo rights in the development databases, which implicitly gives them all necessary rights on stored procedures i.e., execute, view definition, etc.
However a user, even one with dbo rights, cannot explicitly grant permissions to themselves. So when the stored procedure is scripted out from development, it only contains the explicitly granted permissions and not the implicit ones. Developers only have datareader rights in the Test and Production databases, so they are unable to view the definition of a stored procedure in those environments to determine if it is identical to the one in production.
I am just trying to determine if there is any way a developer can grant himself view definition permission on a stored procedure he has created in development so that permission can be carried forward to Test and Production.

This is why you want to use a Role.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 13

Alternatively he could use a Windows security group - if he is the only member of that group granting permissions to the group would effectively grant it to himself.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 14

Maybe you could create a dummy user in the db, have the main user EXECUTE AS that user to give him/herself permission, then REVERT back to their own id for all other tasks?!?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".