Re: Table OWNER

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 18:39:15 -0000

Hi

Take bdyck out of sysadms, you should never give a user more privileges than is necessary.

Have individuals own their own tables may make administration more difficult. If necessary you could have separate databases for each user if you really wanted them separate. Look up GRANT in Books online to see about granting access to each of these users and then a single schema may be sufficient for your needs.

John
  "Saradhi" <upadrasta@inooga.com> wrote in message news:eeGzbEN7EHA.2124@TK2MSFTNGP15.phx.gbl...
  Hi,
  Thanks once again.
  Let me tell you what I am trying to do.
  I wrote DB script and created a lot of tables by putting the dbo in the Script itself as

  CREATE TABLE [bdyck].[PES_SYSTEM] (
   [DOMAIN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [SETTING_STRING] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [SETTING_VALUE] [float] NULL
  ) ON [PRIMARY]
  GO

  So now the Owner of the Table is dbo.
  But Now we want a secuirty where in only those users who owns this Table (Owner of the Table) can enter into the Application.
  So bdyck is the user I wanted to create by default now. So I need to change the owner of the PES_SYSTEM table and other tables to bdyck and hence used the previous Cursor to do it. Now I can see the owner for all my tables changed to bdyck in the SQL Enterprise Manager.
  But the SQL code I wrote in the application is invalid as the SQL is looking for the tablename preceded by "bdyck" even though I loggedin with the userid bdyck.
  private bool IsUserExistsInSecurity(string userName)
  {
  bool bResult = false;
  switch (mDbManager.DBType)
   {
    SqlConnection conn = (SqlConnection)mDbManager.DBConnection;
    SqlCommand cmd = conn.CreateCommand();
    string sql = "SELECT count(*) FROM PES_USERS where UPPER(userid) = '" + userName.ToUpper() +"'";
    cmd.CommandText = sql;
    int version = 0;
    version = int.Parse(cmd.ExecuteScalar().ToString(),CultureInfo.InvariantCulture);
    if ( version == 1 )
    {
     bResult = true;
    }
    break;
    return bResult;
   }
  }
  .NET Throwing an exception saying that PES_USERS is an invalid object.

  Can you please help me whats the problem and the approach I am following.
  If my approach is wrong, Can you tell me the right Approach I need to follow?

  With Rergards,
  SARADHI
    "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:Oowd2NM7EHA.3700@tk2msftngp13.phx.gbl...
    Hi

    As bdyck is a sysadmin then the owner will be dbo. If you look at CREATE TABLE in Books online:

    Members of the sysadmin fixed server role, or logins aliased to the dbo user are associated with the user ID dbo; therefore, tables created by these users default to having dbo as the owner.

    John

      "Saradhi" <upadrasta@inooga.com> wrote in message news:OEbPwtL7EHA.3076@TK2MSFTNGP15.phx.gbl...
      HI,
      Thanks for your quick Reply.

      According to the article in the SQL online.
      SQL Server looks for an object in the database in the following order:
        1.. Owned by the current user.
        2.. Owned by dbo.
      If the object is not found, an error is returned.

      For example, user John is a member of the db_owner fixed database role, but not the sysadmin fixed server role, and creates table T1. All users, except John, who want to access T1 must qualify T1 with the user name John. If T1 is not qualified with the user name John, SQL Server first looks for a table named T1 owned by the current user and then owned by dbo. If the current user and dbo do not own a table named T1, an error is returned. If the current user or dbo owns another table named T1, the other table named T1, rather than John.T1, is used."

      So I logged in as bdyck and since I changed the owner of the table PESkyTable to bdyck, I should access PESkyTable without prefixing with bdyck as bdyck is the owner of the table and has Sysadmin Role.

      Wt I am missing here?

      -SARADHI

        "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:eVMoaad6EHA.2568@TK2MSFTNGP11.phx.gbl...
        Hi

        You will need to specify the owner of the object in your select statement something like:

        SELECT * FROM bdyck.PESkyTable

        If you don't specify the owner then it will look for an object owned by the current user, if that does not exist it will look for one owned by dbo.

        See the topic "Database Object Owner" in Books online for more details.

        John
          "Saradhi" <upadrasta@inooga.com> wrote in message news:eGc79zb6EHA.3236@TK2MSFTNGP15.phx.gbl...
          Hi,
          Thanks For your quick Reply.
          Now I wanted to change the owner of all my tables to 'bdyck'.
          So I used the following Cursor to do this.

          DECLARE ChangeTableOwner_cursor CURSOR FOR

          SELECT '''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', bdyck'

          FROM INFORMATION_SCHEMA.TABLES

          WHERE

          OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

          AND TABLE_TYPE = 'BASE TABLE' /*Check the TABLE_TYPE against 'VIEW' to work with views*/

          AND TABLE_SCHEMA = 'dbo' /*To change the owner of tables owned by 'dbo' only*/

          AND TABLE_NAME LIKE 'PES%' /*To change the owner of tables starting with a particular pattern*/

          GO

          DECLARE @sql1 varchar(500)

          OPEN ChangeTableOwner_cursor

          -- Perform the first fetch.

          FETCH NEXT FROM ChangeTableOwner_cursor INTO @sql1

          -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

          WHILE @@FETCH_STATUS = 0

          BEGIN

          PRINT 'SQL: ' + 'EXEC ( sp_changeobjectowner + @sql1 )' + @sql1

          -- This is executed as long as the previous fetch succeeds.

          EXEC ( 'sp_changeobjectowner' + @sql1 )

          FETCH NEXT FROM ChangeTableOwner_cursor INTO @sql1

          END

          CLOSE ChangeTableOwner_cursor

          DEALLOCATE ChangeTableOwner_cursor

          GO

          But interestingly when I go and access one of my table, it says the object doesnt exists in the DB.

          Is there anything I need to change the owner of all the tables to some user and still use the data in the tables?

          -SARADHI

            "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:uu$EMsD6EHA.208@TK2MSFTNGP12.phx.gbl...
            Hi

            "Saradhi" <upadrasta@inooga.com> wrote in message
            news:OfVRfVD6EHA.3504@TK2MSFTNGP12.phx.gbl...
            1) Wts the query to know the Owner of a table?

            Select * FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = '<Table Name>'

            2) I logged in as a user pardha whose server role is a system administrator.
            I created a table in the SQL Enterprise Manager and SQL Query Analyser, but
            the EM shows the owner of the Table as dbo. it should show as pardha as I
            created it. Is there anything wrong in my thinking?

            As you are (probably) a member of the sysadmin fixed server role you will be
            mapped onto dbo.
            http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_9qyh.asp

            John

            -SARADHI