February 5, 2015 at 2:13 pm
I want to provide access to one of my users to a database on the production server. I do not want this user to be able to view anything other than the tables in that database, or the other databases on my production server.
I gave him access to one DB - TestDB- and made him data_reader on that DB. I had set that as his default database. However, when he logs in using SSMS he can see from Object Explorer a listing of all the databases on that server, although he can't access any of those. This is an external user and I don't want him to see any of that stuff, including other objects (SPs, Views, etc.,) even within TestDB.
To summarize, I want to grant access to a windows user to see/select from ONLY tables in TestDB of my production server, and I do not want him to be able to see any objects other than tables of this DB from SSMS.
Is that possible? I would apprecite your inputs.
SQLCurious
February 5, 2015 at 3:37 pm
First, I'm assuming that these user login accounts are properly assigned as members of default [public] server role and NOT [sysadmin]. There is nothing you can do to hide things from members of [sysadmin].
By default, public members can "see" all user databases, meaning they are at least listed in SSMS Object Explorer, even if they cannot access them. This can be confusing, especially if there are many databases.
Starting with SQL Server 2005, there is a collection of VIEW permissions that can be used to grant or deny visibility of objects. The following will prevent SSMS from listing any databases.
DENY VIEW ANY DATABASE TO [testuser];
Unfortunately this means they can not see even databases for which they have access, which means navigation within Object Explorer is crippled. They can still USE <DATABASE> and execute SQL queries in their databases, but they now can't use Object Explorer. I don't know of a method to grant visibility to only databases for which a user has access or to a specific database. It doesn't appear to be a case usage scenario that GRANT VIEW syntax supports.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply