Re: Error Granting DB / Role Access

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

Date: Tue, 18 Jan 2005 08:33:02 -0800

Jasper,

Okay, I think I may have something that works. I moved the scripts to grant
access to tempdb to a stored procedure within the master database. I then ran
the proc, and went into EM to verify the permissions were there and that the
error wouldn't show up. And it worked. When I went into EM and into the
security for the user account, I didn't receive the error, which is great,
but still weird. Why would it work from executing a procedure, but not work
when directly entered into Query Analyzer? At least it works, and I can
proceed from there.

Thanks for all of your help!
Jason

"Jasper Smith" wrote:

> Gotcha. One option is to add the user to the db_owner role in model but you
> will have to remember to to remove it from new databases so that's probably
> not that useful but you can create a startup proc to do it e.g.
>
> CREATE PROCEDURE dbo.usp_adddevtotempdb AS
>
> exec tempdb..sp_grantdbaccess 'myuser','myuser'
>
> exec tempdb..sp_addrolemember 'db_owner', 'myuser'
>
> exec sp_procoption N'usp_adddevtotempdb ', N'startup', N'true'
> GO
>
> --
> 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:DFCA2523-3D9A-482F-A724-B656766A5C02@microsoft.com...
> > 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
> >> >
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: Thank you very much...again
    ... The reason that we need to grant ... delete..etc permissions on tempdb. ... >database which would avoid some of this. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedures in the Master Database
    ... The error occurs on a GRANT, not EXEC. ... >>I have a stored procedure that I want to make available to ... >>following command in the master database: ...
    (microsoft.public.sqlserver.security)
  • RE: Security context of stored procedure
    ... Suppose there are three users in a database TEST_CHAIN, ... simple stored procedure: ... also grant it to test_sp2 ... SELECT permission denied on object 'authors', database 'test_chain', owner ...
    (microsoft.public.sqlserver.security)
  • Re: Computed columns in temp tables
    ... create the function in tempdb whenever SQL Server is started. ... will not just disappear from tempdb once you have created it, ... >> Now using another database u can call this function during temp table ... >>> How can I create a function in tempdb while inside a stored procedure? ...
    (microsoft.public.sqlserver.datamining)
  • Re: Computed columns in temp tables
    ... create the function in tempdb whenever SQL Server is started. ... will not just disappear from tempdb once you have created it, ... >> Now using another database u can call this function during temp table ... >>> How can I create a function in tempdb while inside a stored procedure? ...
    (microsoft.public.sqlserver.server)