Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Smile Unanswered: Security for Sql server Objects( including DBA)

    How to hide tables objects?

    How to create trigger which can't be disabled by DBA?

    we provide client the whole Database but want to control the access
    on DATABASE at our end so that even DBA can't view and alter anything?

    give recommendation and help........guidance

  2. #2
    Join Date
    Jun 2011
    Posts
    2

    Thumbs down same question

    Quote Originally Posted by ankush sarna View Post
    How to hide tables objects?

    How to create trigger which can't be disabled by DBA?

    we provide client the whole Database but want to control the access
    on DATABASE at our end so that even DBA can't view and alter anything?

    give recommendation and help........guidance
    CREATE TRIGGER safety3
    ON ALL SERVER
    WITH ENCRYPTION
    FOR DROP_SYNONYM,DROP_TABLE,DROP_TRIGGER,DROP_VIEW,DRO P_FUNCTION,DROP_PROCEDURE,CREATE_TABLE,CREATE_VIEW ,CREATE_PROCEDURE,CREATE_TRIGGER,
    CREATE_FUNCTION,CREATE_SYNONYM,ALTER_TABLE,ALTER_V IEW,ALTER_PROCEDURE,ALTER_TRIGGER,ALTER_FUNCTION

    AS
    IF DB_ID('Registration') = (select dbid from sys.sysdatabases where name='Registration' )
    ROLLBACK TRANSACTION ;
    ELSE IF DB_ID('DUMMY') = (select dbid from sys.sysdatabases where name='DUMMY' )
    ROLLBACK TRANSACTION ;
    ELSE
    print 'easy';
    go
    -------------------------------------------------------------

    i have created this trigger on server but it can be disabled..

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    From SQL Server 2005, a user can only see objects on which it has some access. To hide the tables, you have to revoke access to them. For instance, you can hide the complexity of your data model by denying access to all table objects, and only grant access to a the needed views to retrieve needed data.

    Removing DBAs ability to create/alter/delete anything would in my humble opinion not be a very good idea. A good DBA may want to create procs for performing administrative tasks, like for instance index management (optimization and/or monitoring)
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •