Re: Stored Procedure Security Problem
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 2 Nov 2007 11:07:22 -0500
in SQL Server 2000, "schema_1" was created in T-SQL by running:
CREATE SCHEMA schema_1 AUTHORIZATION schema_1
This isn't valid SQL 2000 syntax, even though an error isn't raised. As you can see from the following SQL 2000 Books Online excerpt (http://msdn2.microsoft.com/en-us/library/aa258256(SQL.80).aspx), SQL 2000 syntax does not include schema name. The specified owner must be an existing user and, as far as I know, is only used to validate that the specified user exists:
CREATE SCHEMA AUTHORIZATION owner
[ < schema_element > [ ...n ] ]
< schema_element > ::=
{ table_definition | view_definition | grant_statement }
Despite the name, a CREATE SCHEMA statement in SQL 2000 doesn't create a schema in the SQL 2005 sense. It simply allows you to create multiple objects as part of an atomic statement without specifying objects in dependency order. You still need to owner-qualify objects and grant permissions as you would normally.
However user_1 does not seem able to run it. The error message mentions
"anonymous logon" however NT authorization is used and all other dbo.__
objects are selected and executed without a problem.
Strange that the error includes "anonymous logon". Can you post the full error message text? What does "SELECT USER" return when this user connects to the database using Windows authorization?
dbo can execute [schema_1].[SP_1] without any issues.
In SQL 2000, the database owner has full permissions over all objects in the database regardless of owner (schema). The object owner (same as schema name) has full control over all owned objects. All other uses need to be granted permissions either directly or via role membership.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Lolik" <Lolik@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:191FB4C9-F307-4082-BA16-20BDE96359E7@xxxxxxxxxxxxxxxx
I wonder if my problem is related to this same issue:
in SQL Server 2000, "schema_1" was created in T-SQL by running:
CREATE SCHEMA schema_1 AUTHORIZATION schema_1
then [schema_1].[SP_1] was created by dbo:
select *
into ##temp_table
from schema_1.view_1
user_1 who is not owner of schema_1 (and was not granted permission in
connection with schema_1) was given execute permission on schema_1.SP_1 using
Enterprise Manager.
However user_1 does not seem able to run it. The error message mentions
"anonymous logon" however NT authorization is used and all other dbo.__
objects are selected and executed without a problem.
dbo can execute [schema_1].[SP_1] without any issues.
Please help!
.
- References:
- Re: Stored Procedure Security Problem
- From: Dan Guzman
- Re: Stored Procedure Security Problem
- Prev by Date: SAMBA 4: Will it work with SQL Integrated Security?
- Next by Date: Re: why the permissions don't work anymore?
- Previous by thread: Re: Stored Procedure Security Problem
- Next by thread: Re: Stored Procedure Security Problem
- Index(es):
Relevant Pages
|
