November 4, 2011 at 8:56 am
I have a few users who can't see 3 new recently created columns on a few tables in a database. this happens on 2 servers. the first is the main production copy of a database and the second is an archive copy.
In both cases the tables were created on the same day a few years ago and 3 columns with a numeric data type added recently. it's these columns that people can't see.
i thought it was a MS Access issue but it happens on SSMS as well. the people in this case are part of a windows group that is mapped to a SQL login that have R/W access to the tables in question. I did some testing and replicated this with a current R/O login and a new R/O login that i created specifically for testing and assigned it datareader permissions.
i also replicated this issue in QA by restoring a copy of the db from backup. i thought it might be corruption so i restored the db, ran dbcc checkdb and there was some minor corruption of a clustered index having a pointer to data that was deleted years ago. so i dropped all indexes including the PK and recreated the PK and clustered index and i still cannot see the columns using a new R/O login that i created.
November 4, 2011 at 9:08 am
If your users are attempting to see the new columns in SSMS, they need to refresh first before they can see them. This one has caught me out before!
John
November 4, 2011 at 9:12 am
i replicated this on a virtual XP instance i have for testing
in MS Access the columns aren't visible at all when linking to the table
in SSMS they are there but no data type is specified and it only says null after the column name when connecting with a R/O SQL login. all the other columns on the table show the name, data type and null or not null.
i think i tried to specifically give select permission to the columns in question and the issue still persists and follows the database to other servers when it is restored
just ran checkdb on a restored copy after dropping and recreating the indexes. no corruption but i still can't see the columns
when i log on as myself or sa i can see the columns in Access and SSMS just fine. i only have these issues with a R/O login
November 4, 2011 at 10:07 am
I am wondering if the person who originally granted the SELECT permission on that table explicitly specified the columns in their GRANT statement. This would explain why columns added to the table later would not be selectable. What does this return on your system?
EXEC sys.sp_helprotect
@name = N'your_schema_name.your_table_name' ;
The last column in the rowset is named "Column". If it does not say "(All+New)" in the row corresponding to your users that's why they do not have permission to select from the recently-added columns.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply