2

So I am trying to use Visual studio and I can't import the DEV database into a project. I run into this issue a couple times a week since I am either creating SSIS Packages, or building a database from scratch, or creating SSAS models that require me to hit many databases, create job schedules....

My current error is that I can't reverse engineer the DEV database in Visual Studio because I don't have view definition. This is probably one of the more frequent issues I run into. I had the same error when I tried to see an Execution plan.

So based on this article http://sqlmag.com/database-security/security-options-developers-devsandbox-environments I am wondering if Grant Control on Schema will cover everything I need.

Another issue that comes up is that I can't query the database to see who might be locking my query. Running SP_who2 only tells me what I am doing. It is quite frustrating to have to turn in a ticket and wait for a DBA to figure out the issue. What right do I need to have to run SP_WHO2 or run a query to see what is hitting the system.

And should I not be able to kill my own SPID when something goes awry? The stop button in SSMS does not always work.

I'd also like to run Brent Ozar's spBlitz tools so I can tune my own queries. Should that not be a developer's task. I'd like to know if I create an index and it is ever hit and how often. Or should I include a column I need in another covering index.

Thoughts? Will Grant Control give me everything I need as a Database developer, not to be confused with a report writer, or someone writing basic procs for an app.

Improve this question
3
  • Btw, the stop button not "working" depends on the type of batch of transactions you ran. For example, using the delete statement requires everything to be logged first. This also means rolling back changes must be reversed in the same order as the log. Another reason the log file is critical, too
    – clifton_h
    Aug 11, 2017 at 4:31
  • 2
    And frankly, ask a Dba to supply you with relevant details if you must. It's ok not to have power because then you would be responsible for failures, too. ;)
    – clifton_h
    Aug 11, 2017 at 4:32
  • ohhh... @clifton_h that last phrase about responsible for failures, so true and usually most of the ones requesting access when you ask about it quickly say "noooo! that's when you come into play" then forget about any permissions...not that the OP is one of those cases, but I see this on weekly basis
    – Yaroslav
    Aug 11, 2017 at 9:20

1 Answer 1

Reset to default
6

I am wondering if Grant Control on Schema will cover everything I need

Will Grant Control give me everything I need as a Database developer.

But you didn't write what exactly you need as database developer. If you update your question with your tasks to accomplish, I'll answer on that. For now I can give you only general answer.

Having CONTROL on schema you have access to this schema objects only, i.e. you can

  • SELECT INSERT UPDATE DELETE and ALTER tables (but you cannot CREATE new table because this requires CREATE TABLE permission)
  • ALTER and EXECUTE modules (but not CREATE new ones because you don't have CREATE PROCEDURE/VIEW/FUNCTION permission)
  • GRANT permissions on your schema objects to user but not CREATE USER

You are still lacking SHOWPLAN permission having only CONTROL ON SCHEMA and of course you continue to see only your own sessions using sp_who2

To see all the sessions on the server you need VIEW SERVER STATE server level permission.

To be able to see plans you need SHOWPLAN permission in all databases involved in your query (so if you use temp tables you cannot see the plan if you have no SHOWPLAN in tempdb)

To be able to create jobs you must be a member of SQLAgentOperatorRole

To be able to kill any session you need ALTER ANY CONNECTION server level permission.

To use stop button in SSMS you don't need any special permission

I'd like to know if I create an index and it is ever hit and how often

To be able to select from sys.dm_db_index_usage_stats you need VIEW SERVER STATE permission.

.............

It seems to me I touched all the actions mentioned in your question, if I miss smth please ask in a comment or update your question

Improve this answer
2
  • Thanks for the response. It looks like I need "View Server State" and "SHOWPLAN" permission on tempDB. I only need to kill my session. I googled it, and it looks I would have to have a certificate created and then a stored proc to limit me to killing my own SPID.
    – Phillip Putzback
    Aug 11, 2017 at 16:11
  • The stop button works the majority of the time, it is when it doesn't that I need the kill option. I've had Power Queries in Excel not stop and even after killing excel, the query still runs on the server. And after opening Excel again and running the query, they will get a new SPID and start running while the other is.
    – Phillip Putzback
    Aug 11, 2017 at 16:19

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