SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Deny access to linked server user
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cha_man
Starting Member

3 Posts

Posted - 03/11/2013 :  09:50:37  Show Profile  Reply with Quote
Hi All,
I was asked to give a table access to a partner company and therefore I created a new login in sql server 2008 and set the permissions so that they won't see the tables that they are not interested in and the stored procedures of our system. It works fine but if they login into SSMS, they can see all the databases we have but no access. we don't want them to see our databases.. so I'm gonna ask them to access the table by creating a linked server to our server db and disabled the login leaving Grant permission to connect to db... when I test I'm not be able to access the table via linked server..
I'm new to sql server 2008 and please let me know the best way to give access to a data table. Can I create a db user with no access to SSMS but can connect to db and access table via linked server...
Hope this makes any sense to you..

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1693 Posts

Posted - 03/12/2013 :  19:26:56  Show Profile  Reply with Quote
You may find that the VIEW ANY DATABASE permission is the answer to your issue. See BOL for details. By default this permission is granted to the PUBLIC role so everyone can see every database. Try to REVOKE this right from PUBLIC and see if that sets you right. You will need to GRANT the right to logins that you want to have it but no longer have it explicitly or as a member of another role.
You cannot create a DB user that doesn't have access via SSMS. Remember that SSMS is just another application. It gets login information from somewhere and tries to login into the SQL Server, just like every other application. It would be possible, in theory, to create a server level login trigger that interrogated the Application Name from the connection string and had enough logic to deny specific users but that would be unnecessarily complicated. SSMS knows about SQL Server but the SQL engine treats SSMS like any other application; either it supplies the proper credentials or ity doesn't get access.
Long answer short - use the VIEW ANY DATABASE permission to control who can see what.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Edited by - Bustaz Kool on 03/12/2013 19:28:54
Go to Top of Page

cha_man
Starting Member

3 Posts

Posted - 03/13/2013 :  09:19:25  Show Profile  Reply with Quote
Hi Bustaz... many thanks for the reply... I tried the VIEW ANY DATABASE permission and when I did revoke that I couldn't see the database that I'm suppose to see and then I had to make the user as db owner and it worked but the problem is then the user can see all the tables and stored procedures as well and I'm unable to deny permission as the user is now db owner... I had enough with this and finally I create a new database with the table I want and created a trigger to update the changes from original table... again thanks for your time..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000