Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Greyed out Column Permissions in SSMS
Published: Dec 03, 2020
I’ve had this question a few times now so I thought I would follow up my last post on column permissions with this little note about the SSMS implementation.

Basically, I’ve had people ask “Why can’t I add Column Permissions in SSMS? It’s always greyed out.”. This is what they’re talking about:



You can see above that the "Column Permissions" box ix grey and inaccessible. So how can you add column permissions if the button is greyed out? Well it’s actually a little misleading but makes sense when you know what it’s doing.

We’ll create a quick user in the AdventureWorks database:

if exists
(
      
select *
      
from sys.sysusers
      
where name = 'bob'
)
begin
       drop
user bob
end;

create user bob without login;


Now we can go back to our Permissions screen and we click Search:


Find and add our user:


We can see that we can still not access user permissions:


The reason is simple, although not obvious at all… you need to use the “Permissions” box at the bottom to check “Grant” against “Select”. Once you have done this the Column Permissions button will suddenly enable:


And now you can press the button and choose your specific column level access:


I have to admit this does seem odd when you first encounter it, but once you realise that you need to select the main permission first and then filter down to column level then it starts to make sense.

Hope this helps.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.