Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cha_man
Starting Member
3 Posts |
 Posted - 2013-03-11 : 09:50:37
|
| 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
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-03-12 : 19:26:56
|
| 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 |
 |
|
|
cha_man
Starting Member
3 Posts |
Posted - 2013-03-13 : 09:19:25
|
| 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.. |
|
|
|
|
|
|
Deny access to linked server user