-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.

  • What exact error message are you receiving for the procedures that "did not carry over"? Is it possible it is a permissions error on an object referenced inside the stored procedure? Perhaps from a different database? – Aaron Bertrand Jan 26 '15 at 20:44
  • I'm not at my desk, and don't remember the number, but the gist of it was that the user lacked EXEC permission on the SP. – blint Jan 29 '15 at 13:55
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.

  • I asked because it's not the behavior I'm seeing with exec permissions. The select permissions carried across from 2008 to 2012, but the exec permissions did not. I have to do more testing. It could be that the problem was in 2008. I had about 150 statements like this: 'grant exec on mysp to rolefoo;' in the edit window; I selected them all and clicked the Execute button, hoping to execute all of the statements in one fell swoop. Maybe they did not all "take". – blint Jan 26 '15 at 19:44
  • I thought the exec had to be granted sp by sp. Didn't realize that there was a global that granted exec on ALL sps and functions in the current db. – blint Jan 26 '15 at 19:46
  • You can grant EXECUTE (or any other database level permission) on a Database, Schema or Object level. – Kenneth Fisher Jan 26 '15 at 19:58
  • Grab a copy of my sqlstudies.com/free-scripts/sp_dbpermissions SP and put it on each server. Then run EXEC sp_DBPermissions 'dbname','rolename' and compare the results. – Kenneth Fisher Jan 26 '15 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.