tags:

views:

115

answers:

2
Q: 

SQL Server 2005 Remote Login Permissions

I have a user Sql Authentication account that has remote access to my database server. The user has a default database, and is only able to access or modify that DB. So far so good. However, I'd like to prevent the user from viewing the other databases that he doesn't have access to anyway.

Inside server permissions, I can deny the user the ability to "View Any Database", but then he can't see even the one database he's allowed to modify. He can still execute SQL against it, but he has no gui access through sql mgmt studio.

Is there a way to set this up properly?

Thanks.

A: 

Try setting Server Role to Public and Database Role to db_owner.

Lukasz Lysik  2009-09-04 19:13:58
I've added the user to the database in question and made him the owner of it. Network Service account is also an owner so that IIS can connect using Windows Auth. How do I change the Role of the server and role of the database?When I add the user to the public role under /Security/Roles the change doesn't take effect and doesn't seem to change anything.
Scott  2009-09-04 19:26:13
A: 

You can allow view in SSMS if your remote login owns the database.. which is different to db_owner

gbn  2009-09-04 19:23:00
Thanks gbn, that worked, but I don't know why? Doesn't sp_changedbowner effectively do the same thing as right-clicking users, adding the username and then checking db_owner under Role Members?Is there a way also now to prevent the user from viewing the other admin areas, such as "Security", "Server Objects", "Replication" and "Management"?
Scott  2009-09-04 19:39:27

related questions

SQL Server, convert a named instance to default instance?
Natural (human alpha-numeric) sort in Microsoft SQL 2005
In MS SQL Server 2005, is there a way to export, the complete maintenance plan of a database as a SQL Script?
Does ReadUncommitted imply NoLock
Query to list all tables that contain a specific column with SQL Server 2005.
Can I maintain state between calls to a SQL Server UDF?
What are the benefits of using partitions with the Enterprise edition of SQL 2005
Upgrading Sharepoint 3.0 to SQL 2005 Backend?
What's the simplest way to execute a query in Visual C++
Can you perform an AND search of keywords using FREETEXT() on SQL Server 2005?
Create a database from another database?
What's the fastest way to bulk insert a lot of data in SQL Server (C# client)
SQL 2005 Book For Optimization Techniques
How do I create a mapping table in SQL Server Management Studio?
Cannot Add a Sql Server Login
SQL Server 2005 - Export table programatically (run a .sql file to rebuild it)
Diagnosing Deadlocks in SQL Server 2005
SQL2005: Linking a table to multiple tables and retaining Ref Integrity?
How to create a new instance of Sql Server 2005
SQL Server 2008 vs 2005 Linq integration
How do you kill all current connections to a SQL Server 2005 database?
Conditional Visibility and Page Breaks with SQL Server 2005 Reporting Services
SQL Server 2005 and 2008 on same developer machine?
Paging SQL Server 2005 Results
SQL 2005 For XML Explicit - Need help formatting