locked
Permissions for Visual Studio Development RRS feed

  • Question

  • Currently we have a lot of security that blocks the usage of the really useful development tools.

    Our operation currently uses WinSQL in order to get intellisense, but it does it very slowly, and annoyingly because many permissions on the SQL Server are denied to us developers. (most having to do with mssqlsystemresource)

    I cannot remember where, but I believe there was a location at MSDN that indicated a list of "SQL Server Permissions" whether they be for tables or procedures, that would open up development options in Visual Studio.

    Like:

               

    Intellisense. (Currently doesn't work because of SELECT permissions on mssqlsystemresource)

    An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlDatabaseOptions: The SELECT permission was denied on the object 'database_filestream_options', database 'mssqlsystemresource', schema 'sys'.
    

    Debugging.  (the minimum of being able to step through my own SQL script.  Not necessarily stepping into stored procedures or triggers on the server, but the script i'm actively running, i should be able to step through).

    Failed to start debugger.  an exception was generated: 'the EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.'

    So, is there a published list of "permissions" that are required for SQL Development with Visual Studio to open up many of these features. 

    WE don't need or want to drop tables, or erradicate schemas, we just want to get a column list when we hit '.' after a table name. :)

    Thanks

    Jaeden "Sifo Dyas" al'Raec Ruiner

      


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.

    Thursday, December 17, 2015 10:11 PM

All replies

  • To use the debugger, you must be member of the sysadmin server role. Personally, I gave up on using the deubgger many years ago. I more hassle to get it work than I actually got out from it. PRINT and SELECT for debug works well.

    For the intellisense problem, it may be that your DBA has gone on a lock-down craze and revoked access to public to system tables and similar. This SELECT returns one row on my instance:

    SELECT * FROM master.sys.database_permissions where major_id =  object_id('sys.database_filestream_options')

    And the result set should have state_desc = GRANT, permission_desc = SELECT and grantee_principal_id = 0 (that is, public).

    Thursday, December 17, 2015 10:27 PM
  • Regarding "list of "SQL Server Permissions"", you might mean Permissions (Database Engine) https://msdn.microsoft.com/library/ms191291. Or in graphic format, see the poster: http://go.microsoft.com/fwlink/?LinkId=229142

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, December 18, 2015 6:14 PM
  • So

    Again I am un-marking answers because it isn't the answer.

    The security team here is bound by NIST protocols, which means that according to government standards all access is "DENIED" until a validated and verifiable reason is provided requiring the need of a privilege being "GRANTED" in order to do one's job.

    So the answer of "Public tables made accessible" is not appropriate. 

    I need the specific list of EXACTLY what SQL Server permissions need to be granted to an individual user (this will be applied to a group) in order to achieve full database IntelliSense through Visual Studio. 

    This means:

    1. Which Tables need Select permissions?
    2. Which System Procedures need Execute Permissions?
    3. Which Databases need Select or Read permissions?

    Thanks

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.

    Friday, January 8, 2016 4:48 PM
  • I'm not sure, but this might be a partial answer to your question. See Troubleshooting IntelliSense (SQL Server Management Studio) https://msdn.microsoft.com/en-us/library/ms173434.aspxwhich says "Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect."

    Basically, if you have permission to use an object, you can use IntelliSense. If you have no permissions on the object, then you can't.

    Sorry, if this isn't what you are looking for.

     

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, January 8, 2016 5:07 PM
  • I don't know about NIST, but if NIST is behind that lock-down craze that prohibited access to sys.database_filestream_options, I sense that the spirit of NIST is that you should not use Intellisense.

    Maybe you look at a model which permits each developer to has his own playground (Developer Edition or LocalDB), where there is no lockdown and where Debugging and Intellisense work.

    Trying to reduce the lockdown to not include the objects referred to by Intellisense will be an uphill battle, as the list could change in the next release - in either way.

    Friday, January 8, 2016 10:28 PM