What is the right solution depends on what you want to achieve. For starters, it's not common to create stored procedures in master. Nor is it recommendable.

Kevin suggested that you should use EXECUTE AS and make the database TRUSTWORTHY. But that is a setting which can open a security hole, and not one to use lightly. My preference is to use certificate signing, and I have an article that discusses this method
in detail on my web site:http://www.sommarskog.se/grantperm.html

However, as I said, creating procedures in master is not a common thing to do, so maybe the real solution to your real problem is completely different.

The root problem with your initial plan is that user-defined roles are "database roles" so the membership in the role only exits in that database. The membership in the Northwind database role doesn't stick with the user when the user switches to the master
database.

I agree that changing the TRUSTWORTHY setting is not a great idea unless you have no other choice. And I agree that putting procedures in master is discouraged, but can have a purpose. It sounds like what you want to do, is create a user-defined database
role in master, and add master users to that user-defined database role, and add permissions to that role.

What is the right solution depends on what you want to achieve. For starters, it's not common to create stored procedures in master. Nor is it recommendable.

Kevin suggested that you should use EXECUTE AS and make the database TRUSTWORTHY. But that is a setting which can open a security hole, and not one to use lightly. My preference is to use certificate signing, and I have an article that discusses this method
in detail on my web site:http://www.sommarskog.se/grantperm.html

However, as I said, creating procedures in master is not a common thing to do, so maybe the real solution to your real problem is completely different.

The root problem with your initial plan is that user-defined roles are "database roles" so the membership in the role only exits in that database. The membership in the Northwind database role doesn't stick with the user when the user switches to the master
database.

I agree that changing the TRUSTWORTHY setting is not a great idea unless you have no other choice. And I agree that putting procedures in master is discouraged, but can have a purpose. It sounds like what you want to do, is create a user-defined database
role in master, and add master users to that user-defined database role, and add permissions to that role.

Below is an example of using the certificate method without enabling the TRUSTWORTHY option. I'd agree that it is much more secure. It does take a bit more effort to setup. As the others have mentioned keeping user data out of system
databases, if possible, is best. It's worth noting that the msdb database has the TRUSTWORTHY option enabled by default.

I have a similar issue but for a completely different reason. I have set up a linked server for my team of (20) developers, and the link itself works fine, and all the users can run queries against the tables in the linked server. However, if any of them
try to get a list of tables in that linked server using object explorer, they get this error (which I don't get as I have admin privileges):

Googling the issue indicates that I need to grant execute permission on this master xp to all my developers. But I want to do what Neil W above was proposing, and that is to grant execute permision on that master xp to a database
role I already have set up in my database (to control the developers access to their own, each other and "development" schemas). I would much rather control all database access through roles, rather than "hard-coding" specific permissions to specific
users, which can become a maintenance nightmare. Surely that is a logical request?

So is there any way I can achieve this using roles? Or do I have to explicitly and individually map and grant access to all 20 team members (which seems to defeat the whole purpose of having "roles" to begin with).

It's always better to start a new thread than piggybacking on an old one. Your problem is quite different from the original one. For one, you don't have control over which stored procedures that Object Explorer uses.

I tested your scenario, and at first I was not able to repro it when I was using SSMS 2012. However, when I tried SSMS 2008 I got the error. So a possible workaround is to upgrade your developers to SSMS 2012. Note that this does not require you upgrade
the server. And starting with SQL 2012 SP1, the full Management Studio is a free download.

If you want to solve the issue with permissions, you could EXECUTE to public on the procedure in question. Else you need to add the developers as users in master, and then grant permission to them. A role in another database is not applicable in master.

However, I'm curious why you have this arrangement with a linked server in the first place. And why would the developers browse the linked server through SSMS and not directly?