SQLTeam.com | Weblogs | Forums

SQL Server Table Access Problems


I want to grant read access to a single table only for 1x user.

I run this:

GRANT SELECT ON [mydb].[dbo].[mytable]
TO [abc\mynewuser];

When I view Security > Users, the icon shows a red down arrow next to mynewuser (which I believe is due to the user not having CONNECT access?)

I'm guessing I need to tweak something at the Server > Security > Logins level?


If you want that user to be able to connect to that db, you can directly grant that authority:

USE [db_name];
GRANT CONNECT TO [abc\mynewuser];


When you grant access to a user that does not have a corresponding login - the user will show as inactive in that database but will still have those permissions when they access the database.

You will see this if you grant a domain account permissions - but that domain account gets access to the system through a windows group and that windows group has access to the database.