Post reply

Want to restrict DBOWNER to Drop database

  • June 22, 2011 at 7:35 am

    #242934

    Hi,

    Is it possible to restirct DBOWNER from droping its own database, i dont want to create any trigger.

    thanks

  • June 22, 2011 at 7:41 am

    #1342376

    You could change the owner to sa or some other login.

    John

  • June 22, 2011 at 7:45 am

    #1342383

    No i can'nt do that, to explain a bit more what i need is.

    we have few database created by few logins, the login which created the database is assigned DBowner permission to that DB. but now i want that they can do anything with the DB except droping it.

    thanks

  • June 22, 2011 at 8:27 am

    #1342431

    Does the account actually need all those permissions? Why don't you remove it from db_owner and just give it the permissions it needs?

    John

  • June 22, 2011 at 8:33 am

    #1342437

    can you suggest me what all permission i need to give him if requestor is saying they can do anything in and with database except dropping it.

  • June 22, 2011 at 8:41 am

    #1342445

    Well, the requestor would say that, wouldn't he? If I were you, I would tell him that he's getting EXECUTE permission on all stored procedures, and nothing else that he can't specifically justify the need for. Likewise if it's a third-party app, lazy vendors will often tell you that you need db_owner (or, worse still, syadmin). If you ask them to tell you what exactly it needs to do that requires such high permissions, it starts to get interesting.

    John

  • June 22, 2011 at 8:45 am

    #1342449

    thanks for the suggestion John,i will talk to the user but coming back to my initial question, is it possible to stop dbowner from droping the DB?

  • June 22, 2011 at 8:51 am

    #1342455

    Probably not. You could try DENY CONTROL TO db_owner (that may not be the correct syntax), but that may not work because CONTROL is implicitly granted to db_owner. Even if it did work, it could have undesired side effects, so try it in a test environment first.

    John

  • June 22, 2011 at 8:53 am

    #1342460

    thanks John for all your help and suggestions

  • June 22, 2011 at 9:29 am

    #1342499

    why don't you want to use a DDL trigger?

    ---------------------------------------------------------------------

  • June 22, 2011 at 9:42 am

    #1342524

    writing a trigger is always a option for me but wanted to check if we have something else than trigger.

  • June 22, 2011 at 9:49 am

    #1342534

    i would think that a replacement role like this would give a user the rights they need to create objects and do any DML stiff, but take away the ability to backup/restore and drop the database:

    would a role like this be a viable option?

    CREATE ROLE [AlmostOwners]

    EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

    --can the users EXECUTE procedures? uncomment if true

    GRANT EXECUTE TO [AlmostOwners]

    --allow the users to see view proc and function definitions

    Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 23, 2011 at 7:45 am

    #1343040

    Hi,

    It would be best to first find out exactly from the useres what they need to use the Db for. e.g if they need to just run reports on the Data then you know its just read rights.

    As for the db owner triggers are the way to go.

  • July 10, 2011 at 9:35 am

    #1350830

    I had a situation where an Admin was installing a Vendor Database application.

    He was setting all of the users and the Vendor Instructions instructed him to make all users members of the db_owner role.

    I looked at the database and it turned out that they needed more than just EXECUTE on the SP's because their code was accessing the tables directly. I created a script to give them the object permissions that I felt that they needed. Probably a little more than they needed but it was better than giving every user db_owner.

    The vendor did not like it 😛 but it was a financial application that had to deal with reporting to the SEC.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 10, 2011 at 10:53 am

    #1350833

    george sibbald (6/22/2011)

    why don't you want to use a DDL trigger?

    I was wondering how you can prevent a member of the db_owner from from disabling or altering the trigger?:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 19 total)

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