Our client were having database server on SQL2000, lets us call it 'A', it was their database and we were having proc 'SP_A' on their server which populates data in a table. Now, this proc has to be executed from a service running on a different machine which has got its own database let us say 'B' which is on SQL2008.

The service calls its own proc 'SP_B' on B and in that proc using link server 'L_forB' the proc 'SP_A' is executed. Since they didn't wanted the service to do anything else other than executing the 'SP_B', a user 'B_User' was created on server A and using this user, link server 'L_forB' was created.

This was working fine till they decided to migrate the server 'A' to SQL 2008.

Now, the user 'B_User' is unable to execute the proc 'SP_A', in 'SP_A' there are number of inner proc calls to generate the run id( an autoincrement value for logging purpose) etc.

Even if we grant 'Execute' rights for the user 'B_User' on the object 'SP_A', the inner procs don't get executed and gives error like 'Inner_proc1' etc. not accesible to the user 'B_User'.

If we grant 'Execute' permission on all the inner procs 'Inner_proc1', 'Inner_proc2' etc. to the user 'B_User', the thing runs.

According to the DBA there, on SQL 2000, he didn't gave any explicit permission to the user 'B_User' other than 'Execute' on 'SP_A' and the inner procs just inherited the permission.

He gives the reason that all users are mapped to public roles and he doesn't need to grant execute permission on objects other than 'SP_A'
Now, my question is what has changed in SQL2008 regarding public role?

Thanking you in advance...