SQL2005: DB-Owner cannot exec stored-proc that he himself created!

Hi Meir,

I've tried your script on my machine and it runs fine.
the SQL Login that created the DB was automatically mapped to dbo user
Could you please give us a more detailed repro including the login creation?

HTH

עמי

* אם תשובתי הועילה לך, לחץ "כן" בשורה הכחולה בחלון משמאל ודרג הודעה זו.




"Meir S., ClearForsest":

Hi all,

Following bug is backward-comatability issue with 2005 vs. 2000.

The script below runs perfectly on SQL2000, but fails on SQL2005.
Please note: I need to run it under an SQL-user, NOT a windows-login.
I grant this SQL-user a db_creator server-role.

What I do in this script is create a DB, create a stored-proc inside it,
and then try to run it.
Just to make things easier: If you uncomment the GRANT statement below, it
works fine.
Yet, on SQL2000 the GRANT was NOT required, PLUS:
The user is the db_owner, for crying-out-loud...
He should be able to run the proc he just created.


Again, let me stress this: When using Windows-Authentication user, the
script runs fine,
which seems also as an inconsistency in SQL2005.

So, is this a bug ?
Must I GRANT explicitly ?

Thanks
Meir




-

USE [master]
CREATE DATABASE kukuriku
Go
USE [kukuriku]

Go
CREATE PROCEDURE mySP1 AS
SELECT GetDate()
Go

Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
-
-

Thnaks Ami,

I tested myself, and you are right - it runs fine.

So I investigated deeper to see why it failed, and here goes:

For application-specific reason, we need our SQL-user to have
ddladmin+dbreader+dbwriter permission on tempdb.
Since tempdb is reset on startup, and we want to keep the tempdb permission,
we actually set these permissions on 'model', and when tempdb is created it
inherits those.
So in-fact, we grant ddladmin+dbreader+dbwriter on 'model' to our user.

If you do that, and then RESTART SQL-server, you will get the error-message.

Now, this is a puzzle for me:
Our intention was that our user will have ddladmin+dbreader+dbwriter to any
DB that he doesn't own.
However - to any DB that he created himself he should have full access.

So now, I'm not so sure about the SQL2000 vs. SQL2005 - perhaps it
fails on 2000 as well (I'll check)

but still - I think this is a bug: the guy creating the DB is the owner,
regardless of 'model' settings.

Meir




"??? ????" wrote in message
news:

Hi Meir,

I've tried your script on my machine and it runs fine.
the SQL Login that created the DB was automatically mapped to dbo user
Could you please give us a more detailed repro including the login

creation?

HTH

???

* ?? ?????? ?????? ??, ??? "??" ????? ?????? ????? ????? ???? ????? ??.

-

"Meir S., ClearForsest":

> Hi all,
>
> Following bug is backward-comatability issue with 2005 vs. 2000.
>
> The script below runs perfectly on SQL2000, but fails on SQL2005.
> Please note: I need to run it under an SQL-user, NOT a windows-login.
> I grant this SQL-user a db_creator server-role.
>
> What I do in this script is create a DB, create a stored-proc inside it,
> and then try to run it.
> Just to make things easier: If you uncomment the GRANT statement below,

it

> works fine.
> Yet, on SQL2000 the GRANT was NOT required, PLUS:
> The user is the db_owner, for crying-out-loud...
> He should be able to run the proc he just created.
>
>
> Again, let me stress this: When using Windows-Authentication user, the
> script runs fine,
> which seems also as an inconsistency in SQL2005.
>
> So, is this a bug ?
> Must I GRANT explicitly ?
>
> Thanks
> Meir
>
>
>
>

>
> USE [master]
> CREATE DATABASE kukuriku
> Go
> USE [kukuriku]
>
> Go
> CREATE PROCEDURE mySP1 AS
> SELECT GetDate()
> Go
>
> Go
>
> EXEC mySP1
>
> Go
> USE [master]
> DROP DATABASE kukuriku

> -
>
>
>

Hi Ami (and all others),

I tested SQL200 again: works just fine with the 'model'ettings.


So the exact way to reproduce the failure is:
1. Define an SQL-user (clearthe password-policy check-box)
2. Server-Roles ==> db_creator
3. UserMapping ==> model ==> ddladmin+datareader+datawriter (+public,
which is already there)
4. SQL-Restart (?)
5. Now the script fails under this SQL-user.


Meir



"??? ????" wrote in message
news:

Hi Meir,

I've tried your script on my machine and it runs fine.
the SQL Login that created the DB was automatically mapped to dbo user
Could you please give us a more detailed repro including the login

creation?

HTH

???

* ?? ?????? ?????? ??, ??? "??" ????? ?????? ????? ????? ???? ????? ??.

-

"Meir S., ClearForsest":

> Hi all,
>
> Following bug is backward-comatability issue with 2005 vs. 2000.
>
> The script below runs perfectly on SQL2000, but fails on SQL2005.
> Please note: I need to run it under an SQL-user, NOT a windows-login.
> I grant this SQL-user a db_creator server-role.
>
> What I do in this script is create a DB, create a stored-proc inside it,
> and then try to run it.
> Just to make things easier: If you uncomment the GRANT statement below,

it

> works fine.
> Yet, on SQL2000 the GRANT was NOT required, PLUS:
> The user is the db_owner, for crying-out-loud...
> He should be able to run the proc he just created.
>
>
> Again, let me stress this: When using Windows-Authentication user, the
> script runs fine,
> which seems also as an inconsistency in SQL2005.
>
> So, is this a bug ?
> Must I GRANT explicitly ?
>
> Thanks
> Meir
>
>
>
>

>
> USE [master]
> CREATE DATABASE kukuriku
> Go
> USE [kukuriku]
>
> Go
> CREATE PROCEDURE mySP1 AS
> SELECT GetDate()
> Go
>
> Go
>
> EXEC mySP1
>
> Go
> USE [master]
> DROP DATABASE kukuriku

> -
>
>
>

In my last reply I had a typo: "SQL200" and you had to guess whether it is
2000 or 2005

So, just to clarify thing - the problem still stands:
I tested SQL2000 and it works fine.
On SQL2005 it fails.

The exact scenario is detailed in my prev reply: grant
ddladmin+dbwriter+dbreader to the SQL-User on the 'model' db.

Ami - can you reproduce now ?

Meir




"??? ????" wrote in message
news:

Hi Meir,

I've tried your script on my machine and it runs fine.
the SQL Login that created the DB was automatically mapped to dbo user
Could you please give us a more detailed repro including the login

creation?

HTH

???

* ?? ?????? ?????? ??, ??? "??" ????? ?????? ????? ????? ???? ????? ??.

-

"Meir S., ClearForsest":

> Hi all,
>
> Following bug is backward-comatability issue with 2005 vs. 2000.
>
> The script below runs perfectly on SQL2000, but fails on SQL2005.
> Please note: I need to run it under an SQL-user, NOT a windows-login.
> I grant this SQL-user a db_creator server-role.
>
> What I do in this script is create a DB, create a stored-proc inside it,
> and then try to run it.
> Just to make things easier: If you uncomment the GRANT statement below,

it

> works fine.
> Yet, on SQL2000 the GRANT was NOT required, PLUS:
> The user is the db_owner, for crying-out-loud...
> He should be able to run the proc he just created.
>
>
> Again, let me stress this: When using Windows-Authentication user, the
> script runs fine,
> which seems also as an inconsistency in SQL2005.
>
> So, is this a bug ?
> Must I GRANT explicitly ?
>
> Thanks
> Meir
>
>
>
>

>
> USE [master]
> CREATE DATABASE kukuriku
> Go
> USE [kukuriku]
>
> Go
> CREATE PROCEDURE mySP1 AS
> SELECT GetDate()
> Go
>
> Go
>
> EXEC mySP1
>
> Go
> USE [master]
> DROP DATABASE kukuriku

> -
>
>
>

Can u put the scripts here, I did a test and it worked fine.

Regards.
"Meir S., ClearForsest" escreveu na mensagem
news:

Hi all,

Following bug is backward-comatability issue with 2005 vs. 2000.

The script below runs perfectly on SQL2000, but fails on SQL2005.
Please note: I need to run it under an SQL-user, NOT a windows-login.
I grant this SQL-user a db_creator server-role.

What I do in this script is create a DB, create a stored-proc inside it,
and then try to run it.
Just to make things easier: If you uncomment the GRANT statement below, it
works fine.
Yet, on SQL2000 the GRANT was NOT required, PLUS:
The user is the db_owner, for crying-out-loud...
He should be able to run the proc he just created.


Again, let me stress this: When using Windows-Authentication user, the
script runs fine,
which seems also as an inconsistency in SQL2005.

So, is this a bug ?
Must I GRANT explicitly ?

Thanks
Meir




-

USE [master]
CREATE DATABASE kukuriku
Go
USE [kukuriku]

Go
CREATE PROCEDURE mySP1 AS
SELECT GetDate()
Go

Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
-
-