Post reply

Remove permissions on sysobjects table for db user sql 2012 like in ms sql 2000

  • August 15, 2012 at 10:48 pm

    #279837

    In a bygone age in ms sql 2000, if I wanted to remove a db users ability to view the sysobjects table in Ent. Manager I'd find the user in the database, right click him, select properties and then click permissions, scrolls down to sysobjects and put a 'x' into the ability to select.

    I can't figure out how to do this in SQL 2012. Please help.

  • August 16, 2012 at 2:10 am

    #1526380

    In SQL 2005 and above, unless a user has some permissions on an object, they cannot see that the object exists via the system views. So if a user (as an example) has only execute permissions on procedures and no permissions on the base tables, when they query sys.objects they won't see any of the tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 3:37 am

    #1526428

    Thanks for the reply, and forgive the newb questioning.

    select * from sys.objects when logged in as website user gives a list of tables.

    I take it that user has more power than is necessary or wanted.

    He's on as db-owner. How do reduce the capabilities this user to prevent querying sys.objects.

    bendecko

  • August 16, 2012 at 3:41 am

    #1526429

    sqlservercentral 58005 (8/16/2012)

    Thanks for the reply, and forgive the newb questioning.

    select * from sys.objects when logged in as website user gives a list of tables.

    That means he has some permissions on those tables (select, insert, update or delete). If he shouldn't be able to see the tables, remove those permissions.

    He's on as db-owner.

    There's the problem. If he's db owner he can do anything he likes to the database. If that's too many rights, start by removing him from db_owner and cutting down permissions to the minimum he should have

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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