January 25, 2010 at 8:07 am
I need to be able to query all the names and sids of the rows in master.sys.server_principals.
But I've discovered that this system view returns different rows depending upon who is logged in.
If the logged in user has sysadmin privs they can see all the rows. If not, they only see a subset.
Any ideas how I can have a low privilege user view the name and sid column values for all rows?
I thought of trying to set up a stored proc using the "execute as" clause tied to a high privilege user windows login, but I'm obviously not doing something right, because it doesn't like that.
January 27, 2010 at 9:05 am
Hey David,
Without knowing the exact error you were seeing with the “Execute As” option, I am thinking that it was because this system view will always be outside of the database context wherein this would be executed and in order to access external resources the database would need to be set to trustworthy (Alter Database Set Trustworthy On) which might be a viable option for you, but keep in mind that this setting is lost upon detach/ attach and restore operations. Another option is to extract this into a more accessible table then create a server-level trigger to track the changes and update this new table some examples of this can be found here: http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4GGLL_enUS350US351&q=sql+server+server+level+trigger+for+login. Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply