SQL Server Performance

Views can be seen but not the data - security

Discussion in 'SQL Server 2005 General Developer Questions' started by jej1216, Feb 1, 2010.

  1. jej1216 New Member

    We have SQL Server 2005, and an outside consultant created views as a 'GOD' login. Now other logins can see the view, but no data is returned. The 'GOD' login returns data from the same views.
    Other logins can see the data from the underlying tables for the views.
    Consultant is no longer with us, so we are trying to see how to correct this. We don't have a DBA - I'm a software developer and I've done some light DBA work out of necessity.
    I don't see anywhere on Server Managament Studio where the views have restrictions set.
    Can someone point me in the right direction?
    Thanks in advance,
    jej1216
  2. Elisabeth Redei New Member

    Hello,
    You can use the following SELECT to see all permissions explixitcly granted on an object (or securable as it is called):
    SELECT * FROM sys.database_permissionsWHERE
    major_id = object_id('NameOfView')
    By default, a database user does not have any permissions so you wouldn't have to set restrictions in order to prevent them from doing things - it is rather the other way around.
    Does this answer your question?
    Kind Regard,
    /Elisabeth
  3. satya Moderator

    WElcome to the forums.
    Does this GOD login a DB owner or under SYSADMIN group?
  4. FrankKalis Moderator

    Hope you haven't paid that consultants' last bill yet. Get him to explain to you what exactly he has done and maybe rectify that so that you are able to use it without calling him everytime

Share This Page