how to provide remote access to databases RRS feed

  • Question

  • SQL Server 2008 R2 on Windows 2008 R2

    I have a sqlserver hosting many databases. as we are streamlining the location of our databases, we need to move some databases to that server. I have managed to reattach the databases and have created a separate login (I don't want to give the sa account to developers) for the developers but from Visual Studio, they are unable to add a connection when they use the login I created for them.

    I have already enabled the login to have permissions on their databases as well as provided all the db_ roles on the user mappings. we still keep getting the error.

    for testing, we use the sa account to add a connection and it worked. but if they use the login account I created for them to do queries, they get the following:

    Unable to add data connection.
    Failed to retrieve data for this request.
    An exception occurred while executing a Transact-SQL statement or batch.
    The SELECT permission was denied on the object 'filegroups',database 'mssqlsystemresource',schema 'sys'.

    how can I create a login for developers that allows them to have full access only on the databases I assign to them without giving them access to the whole server?

    Monday, September 16, 2013 6:52 AM

All replies