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

22/06/2006 - 14:37 por Meir S., ClearForsest | Informe spam

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

Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
-
-

Siga el debate

6 respuestas

Tengo una respuesta

Preguntas similare

Reducir DB SQL2005- SUSDB

Leer las respuestas

#6 Nilton Pinheiro

23/06/2006 - 02:04 | Informe spam

Hello Meir,

This worked fine to me !!

My steps (sql 2005):

Use master
go
sp_addlogin 'nilton','nil#123', 'pubs'

Use Pubs
GO
sp_adduser 'nilton', 'nilton'

sp_addrolemember 'db_owner','nilton'

use pubs
Go
CREATE PROCEDURE mySP1 AS
SELECT GetDate()
Go

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...amp;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

Go

EXEC mySP1

Go
USE [master]
DROP DATABASE kukuriku
-
-

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

1 2

Busqueda sugerida