Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Windows Windows Serveur SQL Serveur SQL2005: DB-Owner cannot exec stored-proc that he himself created!

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

6 réponses
Avatar
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
meir@clearforest.com



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

Go
CREATE PROCEDURE mySP1 AS
SELECT GetDate()
Go

--GRANT EXEC ON mySP1 TO PUBLIC
Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
----------------------------------------------------------------------------
-------------
Signaler un problème avec ce contenu

6 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
עמי לוין
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

--GRANT EXEC ON mySP1 TO PUBLIC
Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
----------------------------------------------------------------------------
-------------





Avatar
Meir S., ClearForsest
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
>
> --GRANT EXEC ON mySP1 TO PUBLIC
> Go
>
> EXEC mySP1
>
> Go
> USE [master]
> DROP DATABASE kukuriku

--------------------------------------------------------------------------


--
> -------------
>
>
>


Avatar
Meir S., ClearForsest
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
>
> --GRANT EXEC ON mySP1 TO PUBLIC
> Go
>
> EXEC mySP1
>
> Go
> USE [master]
> DROP DATABASE kukuriku

--------------------------------------------------------------------------


--
> -------------
>
>
>


Avatar
Meir S., ClearForsest
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
>
> --GRANT EXEC ON mySP1 TO PUBLIC
> Go
>
> EXEC mySP1
>
> Go
> USE [master]
> DROP DATABASE kukuriku

--------------------------------------------------------------------------


--
> -------------
>
>
>


Avatar
Marcelo Colla
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

--GRANT EXEC ON mySP1 TO PUBLIC
Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
----------------------------------------------------------------------------
-------------




Avatar
Nilton Pinheiro
Hello Meir,

This worked fine to me !!

My steps (sql 2005):

-- create login
Use master
go
sp_addlogin 'nilton','nil#123', 'pubs'

-- add user to pubs
Use Pubs
GO
sp_adduser 'nilton', 'nilton'

-- add user to db_owner database role
sp_addrolemember 'db_owner','nilton'

-- Create proc
use pubs
Go
CREATE PROCEDURE mySP1 AS
SELECT GetDate()
Go

-- Open a new connection with user nilton
EXEC mySP1

Result:

-----------------------
2006-06-22 20:52:16.890

(1 row(s) affected)

regards
Nilton Pinheiro
www.mcdbabrasil.com.br

PS: Visite o novo fórum do MSDN, nos vemos lá...
http://forums.microsoft.com/MSDN-BR/ShowForum.aspx?ForumIDR0&SiteID!


"Meir S., ClearForsest" escreveu:

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

--GRANT EXEC ON mySP1 TO PUBLIC
Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
----------------------------------------------------------------------------
-------------