locked
How to restrict user from DDL but allow for DML,DCl,TCL in sql server 2005/2008 RRS feed

  • Question

  • How to restrict user from DDL but allow for DML,DCl,TCL in sql server 2005/2008 ?

    --Rahul D Database Developer

    Wednesday, March 28, 2012 1:38 PM

Answers

  • Grant specific permissions to your appUser and your adminUser. See this chart http://go.microsoft.com/fwlink/?linkid=229142 for the permissions. GRANT ALTER ON DATABASE (or the more restrictive GRANT ALTER ON SCHEMA::<name> and CREATE <some object type>) will allow a user to execute DDL; creating objects.

    DENY ALTER ON DATABASE will block it, though that is not necessary if you didn't grant that permission some other way.


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

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Wednesday, March 28, 2012 3:50 PM

All replies

  • Rahul,

    You can go for DDL triggers, please refer http://msdn.microsoft.com/en-us/library/ms175941(v=sql.100).aspx


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, March 28, 2012 1:50 PM
  • This would not work .  If user disable to trigger then he can do DDL operation

    Can we do below :

    1) Make two user appUser,adminUser

    2) Allow DML,DCL,TCL for appUser and restrict DDL for him

    3) Allow DML,DCL,TCL,DDL for adminUser

    4) Here we can make password of both users different

    5) and give adminUser credential to only SQL SERVER DBA .

    But my question is how we can accomplish point #2,3

    any idea ?


    --Rahul D Database Developer

    Wednesday, March 28, 2012 1:59 PM
  • Grant specific permissions to your appUser and your adminUser. See this chart http://go.microsoft.com/fwlink/?linkid=229142 for the permissions. GRANT ALTER ON DATABASE (or the more restrictive GRANT ALTER ON SCHEMA::<name> and CREATE <some object type>) will allow a user to execute DDL; creating objects.

    DENY ALTER ON DATABASE will block it, though that is not necessary if you didn't grant that permission some other way.


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

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Wednesday, March 28, 2012 3:50 PM