Allow SELECT on Tables for Specified Database

Question

Allow SELECT on Tables for Specified Database

2Tall

SSChampion

Points: 14644
More actions
April 7, 2010 at 6:02 am

#220276

Hi. I am looking to allow a couple of users access to SQL Database (SELECT on all tables).
SQL Server 2005 Standard SP2.
I created AD group and added the group as Login to the SQL server. The group inherits the default 'Public' server role.
I mapped the group to the datareader role.
When testing the connection I note the users are able to see all databases (unable to connect) + all system databases.
The users have access to Stored Procedures, Functions, etc.....
I had hoped the user would only see the database to which they were mapped, nothing else (wouldn't this be a more secure approach?)
Can anyone advise if it is possible to only display the database the user is mapped to.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

mangeshd Right there with Babe Points: 744 More actions · Answer 1

2Tall,

When you add any login into a database as a user with db_datareader role, they automatically can see the metadata of the database as well. I.e. they can see "definition" of all types of objects inside the database including tables, views, functions, and et al. To avoid the users from seeing the objects, you will need to DENY them "view definition" right on all SPs, Function and Views (So that they can only see the tables)

As far as seeing other databases is concerned, they can see the names of the databases but nothing inside it. They can see the names of other databases since those are fetched from master database where they get by default access through public role.

-Mangesh

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 2

Phil,

If you use the following, the users will not be able to see anything at all except the objects they have access to:

Deny View Server State To {your user};

Deny View Any Database To {your user};

Deny View Any Definition To {your user};

Actually, they won't even be able to view the procedures they can execute, and if I recall correctly - they won't even see those procedures in the list.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

2Tall SSChampion Points: 14644 More actions · Answer 3

Thanks for posting.

I gtt error msg 102, Incorrect syntax near '\' when trying to apply to AD group

Deny View Server State To DOMAIN\AD Group Name;

Can I apply to AD group?

I ended up running:

USE Master

GO

Deny View Server State To Public;

GO

Deny View Any Database To Public;

GO

Deny View Any Definition To Public;

When I logged in as a test user I could still see the System Databases Master + TempDB.

I was able to access stored procedures etc under Master.

I have attached screenshot of view.

In answer to my own question I can add deny to the AD group by selecting properties on the server.

So after denying permission how do I then allow view to a specific database (for members of the AD group). I can GRANT View to a Role.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 4

You have to put the AD group in brackets:

Deny View Server State To [DOMAIN\AD Group Name];

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

2Tall SSChampion Points: 14644 More actions · Answer 5

That worked. However from the research I have done it appears I am unable to explicitly GRANT View to the users default database.

Any thoughts,

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 6

2Tall (4/8/2010)
That worked. However from the research I have done it appears I am unable to explicitly GRANT View to the users default database.
Any thoughts,
Thanks,
Phil.

That is correct - deny supercedes other permissions.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Wouldn't it be a heck of a lot easier to create a couple of synonyms in a new database that had almost no space allocated to it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

2Tall SSChampion Points: 14644 More actions · Answer 8

Hi guys. Rather than deny all can I deny view to specific databases?

Jeff I did not quite follow your suggestion regards the use of Synonyms?

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 9

Guys I am now revisiting allowing access to specified users.

I note if I add Login (default 'Server Role' 'Public') the user can create Logins (albeit with no permissions)? I am surprised this is permitted.

Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper