Viewing Table Permissions with Column Permissions
-
I am testing some very strict permissions rules I have been given to apply to one of our databases. (This is a third-party database so like the rules or not, I am setting them up as needed.)
I am having a problem because when I look at the permissions in EM after I run my script, some of the permissions don't show up the way they should.
The following are some examples. The first image shows one of the tables that shows the proper setup. On this table, I have granted UPDATE permissions to PA - Update while denying update permissions to some of the columns.
The following image shows a table that does not show the proper setup. On this table, I have granted UPDATE permissions to PA - Lease Update while denying update permissions to some of the columns.
In both cases, I have granted UPDATE permissions to PA - Column Permissions. Anyone that needs full update permissions is part of the PA - Column Permissions role and not a member of the PA - Update or PA - Lease Update roles. (This is so they won't be hindered by the DENY permissions.)
I have done this on three tables and one looks right (the first one above) and the other two look wrong.
I have also run the sp_table_privileges script and I show REFERENCES permissions for PA - Update and PA - Lease Update but I don't see UPDATE permissions for either of these groups.
Does anyone know if this is normal and why one looks OK and the others do not?
The permissions seem to be working properly, I am just trying to understand what I am seeing because this can be misleading when trying to verify some permissions.
Thanks for your help.
Hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
Your images didn't come through but you can use the system stored procedure sp_helprotect to show detailed information about permissions. It shows both table/view and column privileges. You'll have to do so as a query, though.
If you want to check permissions on a particular object (I'll use the orders table), do the following:
EXEC sp_helprotect 'Orders'
Object name is the first parameter for the stored procedure, hence the reason that works. If you want to check a user or role, you'll have to specify the parameter @username. For instance, to see what the public role has access to:
EXEC sp_helprotect @username = 'public'
K. Brian Kelley
@kbriankelley -
Thanks Brian. I don't know why the images failed, they were in the post preview.
Anyway, I wasn't aware of that procedure. I ran it and it verified what I expected. The permissions are setup like I want them to be.
Apparently EM and the sp_table_privileges procedure have a problem with a very granular level of permissions.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply