locked
Permissions RRS feed

  • Question

  • Hi,

    I need help for giving below permissions.

    User should not create table,alter the table and drop the table.. But they can update,insert,delete the data inside the table..

    User can create ,alter and drop  the store procedures from dbs..

    My default schama is DBO. I have given ddl_admin role. i am able to give a permission for denying permission for creating, altering the table schema . But Not able give deny Drop table permissions..Similarly i am able to give create,alter store procedures permissions,But not able to grant drop store procedure permission.

    Pls help me how to give the permissions for "NOT DROPPING  the TABLE" and "DROPPING the store procedure"...

    (Atlesat help me to give permission for not dropping the table from db..:( )

    Thanks.


    pols
    Wednesday, January 12, 2011 9:14 AM

Answers

  • CREATE SCHEMA user_1 AUTHORIZATION user (that you won't to drop procedures)

    CREATE PROC user_1.sp8
    AS
    SELECT * FROM t8

     

    --connect to the server as user


    DROP TABLE t8 --

    Msg 3701, Level 14, State 20, Line 1

    Cannot drop the table 't8', because it does not exist or you do not have permission.

    DROP PROCEDURE user_1.sp8

    Command(s) completed successfully.

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Friday, January 14, 2011 12:15 PM
    Wednesday, January 12, 2011 1:14 PM
  • Hi,

    I need help for giving below permissions.

    User should not create table,alter the table and drop the table.. But they can update,insert,delete the data inside the table..

    User can create ,alter and drop  the store procedures from dbs..

    My default schama is DBO. I have given ddl_admin role. i am able to give a permission for denying permission for creating, altering the table schema . But Not able give deny Drop table permissions..Similarly i am able to give create,alter store procedures permissions,But not able to grant drop store procedure permission.

    Pls help me how to give the permissions for "NOT DROPPING  the TABLE" and "DROPPING the store procedure"...

    (Atlesat help me to give permission for not dropping the table from db..:( )

    Thanks.


    pols


    In first place you should know that DDLadmin has to got so many implied permissions , see here http://www.mssqltips.com/tip.asp?tip=1718 . So get your user out of that ddl_admin and start from there. The best way to achieve your requirement would be to follow schema separation for your tables and SPs ,
    having everything in dbo is not going to work for you.By this way you can grant alter on schema::table_schema to user and deny alter on schema::sp_schema to user which will allow you to have drop permissions on table and not on SPs

    If you want i can come up with some sample scripts here


    Thanks, Leks
    • Marked as answer by WeiLin Qiao Friday, January 14, 2011 12:15 PM
    Wednesday, January 12, 2011 8:34 PM

All replies

  •  To grant all "normal" permissions on a table

       GRANT SELECT, UPDATE, DELETE, INSERT on tbl TO user

    ---DROPPING the store procedure

    Requires CONTROL permission on the procedure.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 12, 2011 9:32 AM
  • Sorry using your statement, still i am abl eto delete the table.. pls help me how to proced.. That too i dont want to deny permission for single object.. i need for for all tables.
    Wednesday, January 12, 2011 11:21 AM
  • CREATE SCHEMA user_1 AUTHORIZATION user (that you won't to drop procedures)

    CREATE PROC user_1.sp8
    AS
    SELECT * FROM t8

     

    --connect to the server as user


    DROP TABLE t8 --

    Msg 3701, Level 14, State 20, Line 1

    Cannot drop the table 't8', because it does not exist or you do not have permission.

    DROP PROCEDURE user_1.sp8

    Command(s) completed successfully.

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Friday, January 14, 2011 12:15 PM
    Wednesday, January 12, 2011 1:14 PM
  • Hi,

    I need help for giving below permissions.

    User should not create table,alter the table and drop the table.. But they can update,insert,delete the data inside the table..

    User can create ,alter and drop  the store procedures from dbs..

    My default schama is DBO. I have given ddl_admin role. i am able to give a permission for denying permission for creating, altering the table schema . But Not able give deny Drop table permissions..Similarly i am able to give create,alter store procedures permissions,But not able to grant drop store procedure permission.

    Pls help me how to give the permissions for "NOT DROPPING  the TABLE" and "DROPPING the store procedure"...

    (Atlesat help me to give permission for not dropping the table from db..:( )

    Thanks.


    pols


    In first place you should know that DDLadmin has to got so many implied permissions , see here http://www.mssqltips.com/tip.asp?tip=1718 . So get your user out of that ddl_admin and start from there. The best way to achieve your requirement would be to follow schema separation for your tables and SPs ,
    having everything in dbo is not going to work for you.By this way you can grant alter on schema::table_schema to user and deny alter on schema::sp_schema to user which will allow you to have drop permissions on table and not on SPs

    If you want i can come up with some sample scripts here


    Thanks, Leks
    • Marked as answer by WeiLin Qiao Friday, January 14, 2011 12:15 PM
    Wednesday, January 12, 2011 8:34 PM
  • Thanks for the reply..

    It would be really helpful if you can provide me the sample script.

    All my existing tables are in DBO schema.. Now i want to assign the all sps to new schema...How to do?

     

    Thanks..

     


    pols
    Thursday, January 13, 2011 5:26 AM
  • pols

    Have you seen my script I posetd above. (Actually   you need another schema) otherwise there is no way to get what you want


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 13, 2011 6:45 AM
  • Thanks.. It is working..

     

    Thank you somuch..

    • Edited by joo123 Thursday, January 13, 2011 11:20 AM
    Thursday, January 13, 2011 8:25 AM
  • I am glad it works for you
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 13, 2011 9:37 AM
  • I am glad it works for you
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 13, 2011 9:37 AM
  • I have one more question..

    Now i am able to create ,alter and drop the sp..

    But the developers should execute the sp with out giving the schema name.. Means if they give 'exec sp1' ,its giving an error like 'Could not find stored procedure 'sp1'...

    If i give exec [user_1].sp1 .. then able to execute..  The application team should not mention the schama name in thr code.. what to do ?

    Thanks in Advance..


    pols
    Thursday, January 13, 2011 11:25 AM
  • GRANT EXEC ON SCHEMA::Schemaname TO user

    BTW mentioning the schema is very good thing (in terms of performance)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 13, 2011 11:58 AM
  • Hi Dimant,

    Thankyou somuch for your replies.

    Actually developers dont want to mentioen the schema name in the code.

    I tried to execute the the below statement.

    GRANT

     

    EXECute ON SCHEMA::user_1 TO sumi

    But i got " Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."...

    Do i need to give any other permissions to proceed further?Pls help me..

     

    Regards,

    Pols


    pols
    Monday, January 17, 2011 7:37 AM
  • http://msdn.microsoft.com/en-us/library/ms176060.aspx

    The login should have a default schema to user_1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 17, 2011 8:36 AM