Post reply

Revoke Create, Alter and Drop privileges to a user

  • December 12, 2011 at 8:13 pm

    #247515

    Hi All,

    I am new to SQL Server DBA responsibilities.

    I would like to restrict users to ALTER , CREATE or DROP Stored Procedures but I wanna let them to EXECUTE the Stored Procedures in a Database.

    I have revoked the privileges going to the Schema Securables tab, by right clicking on a user (from Secutity folder in SSMS).

    But still users are able to do all the above mentioned functions..

    Could anyone pls suggest me...

    Thanks

    Ram

  • December 12, 2011 at 10:00 pm

    #1420348

    Instead of REVOKE, use DENY.

    If the user is sysadmin server role member, you cant stop them.

  • December 12, 2011 at 11:08 pm

    #1420358

    ramji543210 (12/12/2011)

    Hi All,

    I am new to SQL Server DBA responsibilities.

    I would like to restrict users to ALTER , CREATE or DROP Stored Procedures but I wanna let them to EXECUTE the Stored Procedures in a Database.

    I have revoked the privileges going to the Schema Securables tab, by right clicking on a user (from Secutity folder in SSMS).

    But still users are able to do all the above mentioned functions..

    Could anyone pls suggest me...

    Thanks

    Ram

    I would suggest start using T-SQL for most of the tasks if you are looking for a SQL DBA career.

    Coming to your question,

    Suppose you want to stop the user "UserA" from modifying or altering the procedures. To do this execute below code:

    USE YourDatabaseName

    GO

    GRANT EXECUTE TO UserA

    GO

    DENY CREATE PROCEDURE TO UserA

    GO

    DENY ALTER TO UserA

    P.S. I don't have the access to SQL Server right now, so forgive syntax error(s) if any.

    Sujeet Singh

  • December 12, 2011 at 11:22 pm

    #1420363

    Granting permissions is easy if you understand how it works.

    I would like to restrict users to ALTER , CREATE or DROP Stored Procedures

    NO user will have any access (except public) until given specifically. So you don't need any REVOKE or DENY unless user get permission with some roles or higher level access.

    but I wanna let them to EXECUTE the Stored Procedures in a Database.

    You need to grant execute permissions to users.

    It would be good (in long run) for you start learning it. Here is something to start with.

    Permissions (Database Engine)

    http://msdn.microsoft.com/en-us/library/ms191291.aspx#_algorithm

  • December 14, 2011 at 7:35 pm

    #1421703

    Thanks all for your time!

    Now it is working after I executed the command:

    DENY ALTER TO "USER"

    🙂

  • December 14, 2011 at 11:45 pm

    #1421736

    Welcome.

    Happy to know that it is working.

    Don't forget to DENY CREATE and DROP as well.

Viewing 6 posts - 1 through 6 (of 6 total)

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