Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to grant database role Execute permission for a function or stored procedure in Master?

Answered How to grant database role Execute permission for a function or stored procedure in Master?

  • Sunday, February 19, 2012 8:53 PM
     
     
    Sign In to Vote
    0

    Lets say that as SA in SQL 2008 I have created a Stored Procedure or Function, myProc, in the Master database.

    I then created a Database Role, MyDBRole, in Northwind and added several Users to it.

    I want members of MyDBRole to be able to execute master.dbo.myProc

    The only way I can seem to do it is by granting:
     
      use master
      grant execute on myProc to public

    But I don't want to grant Execute permissions to Public, just to members of the Northwind MyDBRole group.

    I tried this:

      use northwind
      grant execute on master.dbo.myProc to MyDBRole

    But it responds with:

    Msg 4610, Level 16, State 1, Line 4
    You can only grant or revoke permissions on objects in the current database.

    Any suggestions as to how I accomplish this?

    Thanks.

     

All Replies

  • Sunday, February 19, 2012 9:49 PM
     
     Answered
    Sign In to Vote
    0

    One way is:

    1. Have a database user (e.g. domain\test) in the master database that has execute permissions on master.dbo.myProc

    2. Ensure that user also exists in northwind

    3. Create a sproc in northwind e.g. CREATE PRODECURE xxx WITH EXECUTE AS 'domain\test' AS BEGIN EXECUTE master.dbo.myProc END

    4. Grant execute on xxx to MyDBRole

    5. Lastly ALTER DATABASE northwind SET TRUSTWORTHY ON

    I'm sure there are other ways which are easier and more secure, but above should work as a test.

     
  • Sunday, February 19, 2012 10:50 PM
     
     Answered
    Sign In to Vote
    0

    master is master and that is another database.

    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.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Monday, February 20, 2012 4:21 PM
     
     Answered
    Sign In to Vote
    0

    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.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

     
  • Tuesday, February 21, 2012 12:12 AM
     
     
    Sign In to Vote
    0

    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.

    USE master
    GO
    CREATE PROCEDURE myProc
    AS
    BEGIN
    SELECT @@SERVERNAME
    END
    GO
    CREATE CERTIFICATE myCert
    ENCRYPTION BY PASSWORD = '$trongpa55'
    WITH SUBJECT ='myCert'
    GO
    CREATE USER cert_user
    FROM CERTIFICATE myCert
    GO
    GRANT EXECUTE ON myProc TO cert_user
    GO
    ADD SIGNATURE TO myProc
    BY CERTIFICATE myCert
    WITH PASSWORD = '$trongpa55'
    GO
    BACKUP CERTIFICATE myCert TO FILE = 'c:\test\myCert.cer'
    WITH PRIVATE KEY (FILE='c:\test\mycert.pvk'
    , ENCRYPTION BY PASSWORD = '$trongpa55'
    , DECRYPTION BY PASSWORD = '$trongpa55')
    USE Northwind
    GO
    CREATE CERTIFICATE myCert FROM FILE = 'c:\test\myCert.cer'
    WITH PRIVATE KEY (FILE='c:\test\mycert.pvk'
    , ENCRYPTION BY PASSWORD = '$trongpa55'
    , DECRYPTION BY PASSWORD = '$trongpa55')
    GO
    CREATE PROCEDURE Exec_myProc
    AS
    BEGIN
    EXECUTE master.dbo.myProc
    END
    GO
    ADD SIGNATURE TO Exec_myProc
    BY CERTIFICATE myCert
    WITH PASSWORD = '$trongpa55'
    GO
    GRANT EXECUTE ON Exec_myProc TO MyDBRole
    GO

     
  • Wednesday, May 01, 2013 4:35 AM
     
     
    Sign In to Vote
    0

    Rick,

    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):

    The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

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

    Regards,

    Graeme

     
  • Wednesday, May 01, 2013 8:20 AM
     
     
    Sign In to Vote
    0

    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?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se