locked
Restrict the user to only execute permission on procedure in SQL server 2008 R2. RRS feed

  • Question

  • Hi,

    Restricting user from accessing all the objects in SQL server 2008 R2 but giving only execute permission for two procedure.

    This user should not access the tables or views the procedure is calling.

    Please let me know .

    Thank you

    Wednesday, June 28, 2017 12:42 PM

Answers

  • Sorry ,I missed one more detail.

    Actually this procedure Access data from  a table which is in another database.

    User should not have access to this database but should be able to execute the procedure.

    In that case, you could sign the proc with a certificate based on a user with the needed permissions. The cert would exist in both databases. End users would only need access to the database containing the proc.

    Below is a sample script showing this method extracted from Erland's thorough article on the larger subject of granting permissions via procs (http://www.sommarskog.se/grantperm.html#certcrossdb). This includes a few tweak's of my own ( like semicolons :-) ).

    -- Create a test login.
    CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST';
    GO
    
    -- Create test two databases
    CREATE DATABASE db1;
    CREATE DATABASE db2;
    GO
    
    -- Move to first test database.
    USE db1;
    GO
    
    -- Create certificate in db1
    CREATE CERTIFICATE crossdbcert
       ENCRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds'
       WITH SUBJECT = 'Cross-db test',
       START_DATE = '20020101', EXPIRY_DATE = '20400101';
    GO
    
    -- Save the certificate to disk.
    BACKUP CERTIFICATE crossdbcert TO FILE = 'C:\temp\crossdbcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'She said She said',
                      DECRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds');
    GO
    
    -- Create the certificate user. Note that we do not grant access to testuser.
    CREATE USER certuser FROM CERTIFICATE crossdbcert;
    GO
    
    -- A test table.
    CREATE TABLE testtbl (a int NOT NULL,
                          b int NOT NULL,
                          c int NOT NULL);
    GO
    
    -- Insert some test data.
    INSERT testtbl (a, b, c) VALUES (12, 23, 34);
    GO
    
    -- The certificate user needs to access this table.
    GRANT SELECT ON testtbl TO certuser;
    GO
    
    -- Switch to the second database.
    USE db2;
    GO
    
    -- Welcome the test user to this database.
    CREATE USER testuser;
    GO
    
    -- Signed test procedure.
    CREATE PROCEDURE signed_sp AS
        SELECT a, b, c FROM db1..testtbl;
    GO
    
    -- Give test user right to execute the procedures.
    GRANT EXECUTE ON signed_sp TO testuser;
    GO
    
    -- Import the certificate we created in the first test database into the second.
    CREATE CERTIFICATE crossdbcert FROM FILE = 'C:\temp\crossdbcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk',
                      DECRYPTION BY PASSWORD = 'She said She said',
                      ENCRYPTION BY PASSWORD = 'Helter Skelter');
    GO
    
    -- Delete the file with the certificate (this can be done manually if xp_cmdshell isn't enabled)
    EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output';
    GO
    
    -- Sign the test procedures.
    ADD SIGNATURE TO signed_sp BY CERTIFICATE crossdbcert
        WITH PASSWORD = 'Helter Skelter';
    GO
    
    -- Switch to the test login.
    EXECUTE AS LOGIN = 'testuser';
    GO
    
    -- Run the signed procedure. testuser can now access testdbl, even though
    -- he is not a user of db1.
    EXEC signed_sp;
    GO
    
    -- Back to ourselves.
    REVERT;
    GO
    
    -- Clean up.
    USE master;
    GO
    DROP DATABASE db1;
    DROP DATABASE db2;
    DROP LOGIN testuser;
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, June 30, 2017 6:17 PM
    • Proposed as answer by Uri DimantMVP Sunday, July 2, 2017 7:27 AM
    • Marked as answer by knewsql Wednesday, July 5, 2017 12:34 PM
    Friday, June 30, 2017 6:15 PM

All replies

  • A user by default has no permissions in the database. So all you have to do is to grant execute permissions for the procedures. Assuming that the tables has the same owner as the procedures, then ownership thanks to ownership chaining you don't have to grant permissions for the tables.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Dean Savović Wednesday, June 28, 2017 1:51 PM
    Wednesday, June 28, 2017 12:46 PM
  • Hi knewsql,

     

    Yes, you can grant execute permission to the stored procedure without accessing the content in the related table. Please use the following code directly, note that please use SQL Server User Connection instead of using Admin connection.

     

    GRANT EXECUTE ON [dbo].[stored_procudure] TO [User_name] 

     

    You can refer to this article: http://www.c-sharpcorner.com/UploadFile/ff0d0f/grant-execute-or-view-permission-to-stored-procedures-in-sql633/, many scenarios are mentioned in it.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Teige Gao Thursday, June 29, 2017 6:25 AM
    Thursday, June 29, 2017 6:25 AM
  • HI ,

    Sorry ,I missed one more detail.

    Actually this procedure Access data from  a table which is in another database.

    User should not have access to this database but should be able to execute the procedure.

    Please let me know if you have any question.

    Thank you

    Friday, June 30, 2017 5:48 PM
  • Sorry ,I missed one more detail.

    Actually this procedure Access data from  a table which is in another database.

    User should not have access to this database but should be able to execute the procedure.

    In that case, you could sign the proc with a certificate based on a user with the needed permissions. The cert would exist in both databases. End users would only need access to the database containing the proc.

    Below is a sample script showing this method extracted from Erland's thorough article on the larger subject of granting permissions via procs (http://www.sommarskog.se/grantperm.html#certcrossdb). This includes a few tweak's of my own ( like semicolons :-) ).

    -- Create a test login.
    CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST';
    GO
    
    -- Create test two databases
    CREATE DATABASE db1;
    CREATE DATABASE db2;
    GO
    
    -- Move to first test database.
    USE db1;
    GO
    
    -- Create certificate in db1
    CREATE CERTIFICATE crossdbcert
       ENCRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds'
       WITH SUBJECT = 'Cross-db test',
       START_DATE = '20020101', EXPIRY_DATE = '20400101';
    GO
    
    -- Save the certificate to disk.
    BACKUP CERTIFICATE crossdbcert TO FILE = 'C:\temp\crossdbcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'She said She said',
                      DECRYPTION BY PASSWORD = 'Lucy in the Skies with Diamonds');
    GO
    
    -- Create the certificate user. Note that we do not grant access to testuser.
    CREATE USER certuser FROM CERTIFICATE crossdbcert;
    GO
    
    -- A test table.
    CREATE TABLE testtbl (a int NOT NULL,
                          b int NOT NULL,
                          c int NOT NULL);
    GO
    
    -- Insert some test data.
    INSERT testtbl (a, b, c) VALUES (12, 23, 34);
    GO
    
    -- The certificate user needs to access this table.
    GRANT SELECT ON testtbl TO certuser;
    GO
    
    -- Switch to the second database.
    USE db2;
    GO
    
    -- Welcome the test user to this database.
    CREATE USER testuser;
    GO
    
    -- Signed test procedure.
    CREATE PROCEDURE signed_sp AS
        SELECT a, b, c FROM db1..testtbl;
    GO
    
    -- Give test user right to execute the procedures.
    GRANT EXECUTE ON signed_sp TO testuser;
    GO
    
    -- Import the certificate we created in the first test database into the second.
    CREATE CERTIFICATE crossdbcert FROM FILE = 'C:\temp\crossdbcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk',
                      DECRYPTION BY PASSWORD = 'She said She said',
                      ENCRYPTION BY PASSWORD = 'Helter Skelter');
    GO
    
    -- Delete the file with the certificate (this can be done manually if xp_cmdshell isn't enabled)
    EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output';
    GO
    
    -- Sign the test procedures.
    ADD SIGNATURE TO signed_sp BY CERTIFICATE crossdbcert
        WITH PASSWORD = 'Helter Skelter';
    GO
    
    -- Switch to the test login.
    EXECUTE AS LOGIN = 'testuser';
    GO
    
    -- Run the signed procedure. testuser can now access testdbl, even though
    -- he is not a user of db1.
    EXEC signed_sp;
    GO
    
    -- Back to ourselves.
    REVERT;
    GO
    
    -- Clean up.
    USE master;
    GO
    DROP DATABASE db1;
    DROP DATABASE db2;
    DROP LOGIN testuser;
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, June 30, 2017 6:17 PM
    • Proposed as answer by Uri DimantMVP Sunday, July 2, 2017 7:27 AM
    • Marked as answer by knewsql Wednesday, July 5, 2017 12:34 PM
    Friday, June 30, 2017 6:15 PM