Find solution
How to Restrict Database for One User in SQL Server 2008 R2?

How do i restrict access to MSSQL 2005?

  • Are there any phpmyadmin-like tools for Microsoft SQL Server 2005, or other options to restrict direct access to SQL, but leaving productivity and functionality intact? I am currently in the middle of trying to restrict write access to the production Microsoft SQL 2005 Server databases employed where I work. Being a traditional linux guy, my usual method is MySQL with phpMyadmin UI for the data-jockeys. However this particular environment is a 99% microsoft shop, including the SQL databases. Our current environment involves 2 MsSQL servers, one being a master db and the 2nd a slave replicated database. I want to insert a 3rd server into this little group and have it act as a gateway for users to interact with the production databases, leaving the production databases for web-application access only. Ideally such a solution needs to have full user access controls, centralized location such as a web-service (preferably for IIS, but I'm open to suggestions) and has verbose logging so I can see excatly what queries have been sent and by whom. Web service is entirely optional, as long as it can do centralized logging (4-5 users log to a single location) I have looked at the http://www.iis.net/download/databasemanager, but i am unsure if it will log everything that I want it to. If anybody has experience with this, insight, experiances, pros and cons with this tool are very welcome. As always, every suggestion is appreciated.

  • Answer:

    Is there anything wrong with using http://msdn.microsoft.com/en-us/library/ms174173.aspx? It's a (Windows) desktop application, but should do whatever you need it to. I actually (*gasp*) prefer it to phpMyAdmin.

mrw at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

In terms of restricting write access, this can be done via SQL Server's permissions - grant these users the ability to read and not write, problem solved. SQL Server doesn't have any built-in method of logging queries, so this would have to be handled by either something sitting between the users and the server, or running the profiler constantly which probably isn't the best solution. The tool you linked to needs to be associated with a site in IIS, then have a database connection added from there - this would have to be done on each machine as it runs in the IIS manager, not as a site under IIS. http://www.cibersql.com/csqlwebIDE.aspx seems to be what you're looking for, but it's $700 for a server license.

sanko

Set up their security with either their AD logins or give them SQL logins, give them read-only permissions, and then Management Studio for the interface. I don't know mySQL interfaces, so I don't know if there is some functionality you're expecting to give them that SSMS doesn't have.

Lyn Never

MS SQL Server's Reporting Services doesn't fit the bill?

Lukenlogs

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.