Post reply

Deny access on a table to dbo by sa.

  • June 9, 2008 at 1:26 am

    #188731

    I have set up SQL Server 2005 in my company and I have the sa password. I created a database and a login to the SQL Server. I made that login default db_owner of the database by executing the

    sp_changedbowner 'login_Name'. Using sa I want to create a table in that database and deny select,update and delete on that particular database for this user. Can it be possible since the user is the dbo of the database?

  • June 9, 2008 at 2:07 am

    #825994

    You cannot restrict the permissions of the dbo. If you need to hide the table either create it in a different database or change the database owner, and add permissions to the original user explicitly.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • June 12, 2008 at 10:13 pm

    #828231

    As Andras has already indicated, no. SQL Server figures the true database owner should not be blocked from anything. As a result, it does NO security checks within the database.

    You can have the database owned by a different login and make the user a member of the db_owner fixed database role. Security IS checked for db_owner role members, so you could then do the DENY. Of course, a user with this level of permission can undo the restriction, but that is to be expected.

    K. Brian Kelley
    @kbriankelley

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

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