-1

Can exec permissions (e.g. on a SP) be granted to a role in SQL Server 2008, so that all role members then have the permission; and if the 2008 database were backed up and restored into SQL Server 2012, and a 2012 user were added to the (restored) role, would that 2012 user receive those permissions too?

P.S. I am granting exec permission to individual SPs and functions. So I did this in 2008:

grant exec on sp1 to myrole;
grant exec on sp2 to myrole;
grant exec on sp3 to myrole;
grant exec on sp4 to myrole;
.
.
.
grant exec on sp150 to myrole;

I selected all 150 lines of text in the edit window of SSMS and clicked the Execute button. Did the same thing with the tables and that worked--they all carried over to 2012-- but the exec grants did not carry over to 2012 for some reason.

blint
  • 11
  • 3

1 Answers1

1

That is really the purpose of a role in SQL Server.

CREATE ROLE Test
GRANT EXECUTE TO Test

The role Test now has permission to execute any stored procedure or function on the current database (where the role is created). This is true in every version of SQL Server as far as I know (2000+ at least).

Also yes if you backup your database and then restore it to a 2012 server the role and it's permissions will remain intact. That being said you may have some issues if you don't prepare properly. Logins and Users are different objects in SQL Server. The first is a Server level principal and the second is a Database level principal. The members of your database role will of course be database level principals.

The column SID is used to link server principals and database principals together. When you move your database if the SIDs don't match then the "Users" that are members of your role will be orphaned and you won't be able to connect to the database let alone use the permissions in the role. There are a number of ways to make sure that the SID is the one you want including using the sp_rev_helplogin and my sp_srvpermissions. I discussed the problem in more detail here.

Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116