Re: Error Granting DB / Role Access

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 01/18/05 

Date: Tue, 18 Jan 2005 07:41:51 -0000

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: Confused about dbo
    ... Jasper Smith (SQL Server MVP) ... > database within a SQL Server instance. ... > There are several ways you could have the username dbo. ... > the true owner of the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... You cannot set the recovery model in tempdb. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ...
    (microsoft.public.sqlserver.server)
  • Re: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: System Administrator Implied Permissions
    ... mapped to the 'dbo' user are members of the public and db_owner roles. ... Permissions are not checked for sysadmin role members so all other role ... > database role need to be assigned to any sysadmin role? ... >>> SQL Server initially creates the 'sa' user. ...
    (microsoft.public.sqlserver.security)
  • Re: Delegate Power of God to only 1 database - How?
    ... Guess I'll have to look more closely at the permissions ... >I support the Professional Association for SQL Server ... >> permissions to only that database which can be assigned ... >>>Exactly what is this 'dBO' role you are referring to? ...
    (microsoft.public.sqlserver.security)