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?
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
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
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...
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...
ASKER
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
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
ASKER
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.
ASKER
fyi if it wasn't obvious...I'm talking about restricting the view from the SSMS tool.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did that. He can't see any databases now.
ASKER
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
HTH
grant select on tablename to username