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.

closed as too broad by Erik Darling, Andriy M, mustaccio, Colin 't Hart, Max Vernon Aug 11 '17 at 13:14

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 '17 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 '17 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 '17 at 9:20
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

  • 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. – Developer_46038 Aug 11 '17 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. – Developer_46038 Aug 11 '17 at 16:19

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