Post reply

Prevent create procedure if the user does not have access to the procedure contents.

  • April 21, 2011 at 11:40 am

    #238119

    User user1 has no access to the table dbo.Table1, but the user has access to vw.vTable1 which refers to dbo.Table1. The user cannot run the code SELECT TOP 1 * FROM dbo.Table1 (Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object ...).

    But the user can create a procedure (CREATE PROCEDURE dev.Test AS SELECT TOP 1 * FROM dbo.Table1) and execute the procedure dev.Test and get results.

    I would like to be able to limit the users create procedure, view, function against the objects they have access and if they don't I want it to fail at security level.

    Is there anyway this can be implemented?

    Thank you

  • April 21, 2011 at 3:23 pm

    #1314731

    DENY SELECT ON Table1 TO user1?

  • April 21, 2011 at 3:25 pm

    #1314732

    DENY CREATE PROCEDURE

    http://msdn.microsoft.com/en-us/library/ms186915.aspx

  • April 22, 2011 at 6:55 am

    #1314856

    Users are granted create procedure rights on purpose and not given rights to the table on purpose (and specifically denied rights to drop table). But in their procedures they can write code they have not access to. I would like to prevent procedures created by users doing things they don't have access to.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply