Link to home
Start Free TrialLog in
Avatar of mattkovo
mattkovo

asked on

Lock down user in SQL Server 2008

I added a user login but only want the to view a certain table.  Is this possible and if so then how?
Avatar of Aneesh
Aneesh
Flag of Canada image

you can grant the select permission for that table
grant select on tablename to username
sure you can.
grant access to the abase relevant database only and grant select, insert, update, delete whatever permissions to the user you want..
tahts all you have to do
Avatar of mattkovo
mattkovo

ASKER

The user only has access to the database I grant to him but I want to hide the other databases.  How do I do that?
expand your server security node.
expand the logins and choose the login you want to modify.
right click on it and navigate to the User mapping node and set the user mappings on that page...
That grants him access but he can still see the other databases.
Create a role in your database, assign the permissions to the role and then add the user to the role.

If you want to prevent said person from seeing other databases in the list when using SSMS, Right click on the server name in SSMS and select properties, Permissions, locate the user, highlight and tick 'Deny' for 'View any database'

HTH
I don't have the option 'View any database' when I create the role.  I want to have the user by default not have access to any database or view the database.  
fyi if it wasn't obvious...I'm talking about restricting the view from the SSMS tool.
ASKER CERTIFIED SOLUTION
Avatar of St3veMax
St3veMax
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I did that.  He can't see any databases now.
Never got completely helpful answer.
OK, as a test; what if you make person a dbo of the database; he should be able to see it then. then it maybe playing around with roles.

HTH