Re: Difficult qualified name problem

From: Daniel Pratt (dprREMOVETHISatt71@hotmail.com)
Date: 11/21/02 

From: "Daniel Pratt" <dprREMOVETHISatt71@hotmail.com>
Date: Thu, 21 Nov 2002 11:56:44 -0500

Hi Chris,

"Stamey" <Stamey@PostToTheGroupOnly> wrote in message
news:#BCD$tMkCHA.1616@tkmsftngp10...
> Your solution will be to create procedures that create and drop the table.
> The procedure will look something like this:
> Create Procedure MakeTable As
> Create Table TestTable (TestID Int, TestName VarChar(30))
> GO
> Gant Select, Update, Delete, Insert On [dbo].[TestTable] TO [UserTest]
> /*Must grant permission so that application user can use the table */
> GO
>
> Create Procedure DropTable As
> Drop Table TestTable
> Go
>
> Make sure these procedures are created under an admin user so that they
will
> belong to the admin user. The run under thet owner's security context and
> therefore the object that are bcreated will belong to the admin user,
which
> should equate to DBO. Grant permission to the user your app uses to
execute
> these procedures.

    Thanks for your response. Unfortunately, unless I'm missing something, I
don't think it will work. Even though procedure "MakeTable" is owned by
"dbo", if another non-sysadmin user executes the procedure, the table it
creates ("TestTable") will be owned by that user, not "dbo". This is because
SQL Server does not run procedures in the security context of the
procedure's owner. Having an unbroken ownership chain merely disables
security checking down the line.

    Further, it is not possible to create "TestTable" as owned by "dbo"
explicitly (e.g. "Create Table dbo.TestTable...") unless special permissions
are granted to the executing user (such as membership in "db_ddladmin").

Regards,
Dan

Relevant Pages

  • Help: OWSADM hangs when executing from C# web service
    ... admin user and providing the login credentials through the ... string output = process.StandardOutput.ReadToEnd; ... For some reason when I execute the Startmethod then the OWSADM hangs ... The machine is running Windows Server 2003 Enterprise edition. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Difficult qualified name problem
    ... Make sure these procedures are created under an admin user so that they will ... The run under thet owner's security context and ... All objects in the database are owned by ... > Thanks much for any help with this complex problem. ...
    (microsoft.public.sqlserver.security)
  • Re: Impersonating a domain user for debugging purposes
    ... > As a sysadmin role member, you can execute a SETUSER command to use ... > You can revert back to your own security context by running SETUSER with no ... > test it with my permissions, ... > when UserA attempts to execute SProcA, it fails for them, because they do ...
    (microsoft.public.sqlserver.security)
  • Re: Impersonating a domain user for debugging purposes
    ... you can execute a SETUSER command to use ... You can revert back to your own security context by running SETUSER with no ... test it with my permissions, ...
    (microsoft.public.sqlserver.security)
  • Re: Jet.OLEDB and Access 2003
    ... what permissions does the security context under which ASP is running ... requires at least Read, Write, eXecute and Delete. ... > I'm running an old ASP program that worked just fine w/ Access 2000 ...
    (microsoft.public.vb.database)