Re: Error Granting DB / Role Access

From: Jason Delaune (JasonDelaune_at_discussions.microsoft.com)
Date: 01/17/05

  • Next message: sanjivkm_at_hotmail.com: "Re: Windows Users and Machine Name Change" 
    Date: Mon, 17 Jan 2005 11:37:02 -0800

    Because the developers are using BULK INSERT statements to import data into
    temporary tables within a stored procedure. Since they are not sysadmins,
    they get an error message when the code executes stating that they are not
    the database or object owner (within tempdb) even though they have DBO
    permissions on their database. The only way around the error was to grant
    them DBO access to tempdb. I thought this too was weird, but it was the only
    way I could get it to work.

    Thanks,
    Jason

    "Jasper Smith" wrote:

    > Tempdb has the guest user which allows users access - why explicitly add
    > one?
    >
    > --
    > HTH
    >
    > Jasper Smith (SQL Server MVP)
    > http://www.sqldbatips.com
    > I support PASS - the definitive, global
    > community for SQL Server professionals -
    > http://www.sqlpass.org
    >
    > "Jason Delaune" <JasonDelaune@discussions.microsoft.com> wrote in message
    > news:1461AC5A-2CD0-43F7-8F9A-9ACA3A472E2A@microsoft.com...
    > > Hello all,
    > >
    > > I'm trying to create a script to grant access to the tempdb database upon
    > > system startup. The script below executes correctly.
    > >
    > > use tempdb
    > > go
    > >
    > > exec sp_grantdbaccess N'test001', N'test001'
    > > go
    > >
    > > exec sp_addrolemember N'db_owner', N'test001'
    > > go
    > >
    > >
    > > However, once I go into EM to verify that the permissions were set
    > > correctly, I receive this error message:
    > >
    > > Error 21776: [SQL-DMO]The name 'test001' was not found in the users
    > > collection. If the name is a qualified name, use [] to separate various
    > > parts
    > > of the name, and try again.
    > >
    > > Now the owner of the tempdb database is SA, and the dbo login is mapped to
    > > the SA account. The version of SQL Server in question is 8.00.954.
    > >
    > > Any ideas?
    > >
    > > Thanks,
    > > Jason
    > >
    >
    >
    >

  • Next message: sanjivkm_at_hotmail.com: "Re: Windows Users and Machine Name Change"

    Relevant Pages

    • Re: use of tempdb by union all
      ... > You are certainly correct about table variables using the tempdb database, ... Andrew J. Kelly SQL MVP "Tenaya" wrote in message ... > You are certainly correct about table variables using the tempdb database,> and not being entirely memory resident ... ...
      (microsoft.public.sqlserver.programming)
    • Re: use of tempdb by union all
      ... You are certainly correct about table variables using the tempdb database, ... and not being entirely memory resident ... ... are you stating the SQL Server will use the ...
      (microsoft.public.sqlserver.programming)
    • Tempdb
      ... that when tempdb database will be created so it auto create one user which ... for tempdb so it deletes that user when sql service stop and when the ... that user has been vanished from the sql server tempdb ...
      (microsoft.public.sqlserver.clients)
    • Re: SORT_IN_TEMPDB not use tempdb???
      ... Andrew J. Kelly SQL MVP ... > Does SQL Server test if the tempdb database is on the same hard drive as ... >> Andrew J. Kelly SQL MVP ...
      (microsoft.public.sqlserver.server)
    • Re: SORT_IN_TEMPDB not use tempdb???
      ... Andrew J. Kelly SQL MVP ... > Does SQL Server test if the tempdb database is on the same hard drive as ... >> Andrew J. Kelly SQL MVP ...
      (microsoft.public.sqlserver.datawarehouse)