Unanswered: How to interpret the value returned by Permissions()?
I'm trying to check whether a user has permission to execute some master system stored procedures. For example, select permissions(OBJECT_ID('xp_loginconfig')) returns 2097215.
I have converted the decimal 2097215 to binary 00000000 00100000 00000000 00111111. I guess I suppose to look at the lower 16 bits only. Then I convert the binary 00000000 00111111 back to decimal 63. Then I find the largest decimal that is <= 63 in the table (the one in BOL) which shows the bits used for object permissions that are returned when only objectid is specified. Subtract that number from 63 and repeat this process until the difference is 0. So, in this case, 63 - 32 -16 -8 -4 -2 -1 = 0. This should the user has all the corresponding statement permissions. The upper 16 bits 00000000 00100000 are used to check what can be granted to other users. Since they are = 32 which is corresponding to execute, I believe that user can grant execute permission to other users.
For example, to check for execute procedure permission, it seems that I can use either of the following statement:
if permissions(OBJECT_ID('xp_loginconfig')) & 32 = 32
if permissions(OBJECT_ID('xp_loginconfig')) & 0x20 = 0x20
I hope someone can confirm my understanding. Is there another way to check for permission? Also, my understanding is that I must use master before executing permissions() since I'm checking permissions for master stored procedures.
Yes. These values are stored as sums of binary numbers, allowing a single field to contain more than one piece of information. Your use of the bitwise & operator is the standard method of dealing with them.