June 9, 2010 at 2:10 pm
I need to set up a set of objects (procs and TVF) that members of an active directory group can run without having access to the database where the tables are located.
I tried to do this using signed modules and setting up a certificate user as explained here
http://www.sommarskog.se/grantperm.html#certcrossdb
briefly,
I created a certificate in the database where the tables are located (call it db1)
I created a user for that certificate
I assigned that user select permission on the tables
I created the same certificate in another database (call it db2)
I created stored procedures that use tables in db1
I signed those stored procedures with the certificate
Now for the problenm. If I run those stored procs from a sql login or a named windows login that has execute permission on the procs. They work fine. However if I run then from a windows group login then they fail with the message
The server principal "domain\user" is not able to access the database "db1" under the current security context.
So clearly there is something different about using a windows group login for this purpose. But I can't find any reference to it. Is this a configuration problem on my server(s), or something different about the behavior of windows group logins in general?
June 9, 2010 at 2:40 pm
It looks like I may have my own answer. Or at least my own solution. The server I was testing this on was a dev machine running the sql service as local system. When I switched it to run under a domain acocunt, it worked fine. Although I'm still curious about the particulars if anyone knows why it worked for individual and not groups in that case.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply