I want to allow devlopers to create and alter sprocs in the dbo
schema, but not create or alter tables (in any schema). I tried:
GRANT CREATE PROCEDURE TO <user>
...but it will only allow the user to create procedures within their
own user schema - not in the dbo schema. "Server: Msg 2760, Level 16,
State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
does not exist or you do not have permission to use it."
So then I tried adding the user to the ddl_admin fixed db role and
then executing:
DENY CREATE TABLE TO <user>
...I thought I was OK at first. The user could create dbo owned
sprocs, alter them, and not create tables in any schema. BUT, they
can DROP TABLE! Of course, you can't DENY DROP <object>.
Any idears?
TIA,
-PeterHave you tried to use only the REFERENCES permission on the table for the
user creationg the SP? Check the "Owners and Permissions" topic in Books
OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar
chitec.chm::/8_ar_da_2s4z.htm).
HTH,
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0401021018.12de3b15@.posting.google.com...
quote:|||Thank you for the reposnse, but I think you missed the question. Your
> Env: SQL Server 2000 SP3a on Windows 2k Server SP4
> I want to allow devlopers to create and alter sprocs in the dbo
> schema, but not create or alter tables (in any schema). I tried:
> GRANT CREATE PROCEDURE TO <user>
> ...but it will only allow the user to create procedures within their
> own user schema - not in the dbo schema. "Server: Msg 2760, Level 16,
> State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
> does not exist or you do not have permission to use it."
> So then I tried adding the user to the ddl_admin fixed db role and
> then executing:
> DENY CREATE TABLE TO <user>
> ...I thought I was OK at first. The user could create dbo owned
> sprocs, alter them, and not create tables in any schema. BUT, they
> can DROP TABLE! Of course, you can't DENY DROP <object>.
> Any idears?
> TIA,
> -Peter
reponse is angled towards data access permissions. My question is
about object creation permissions. I want a devloper to be able to
CREATE and ALTER stored procedures in the dbo schema, but not be able
to CREATE, ALTER, or DROP tables or other objects.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<ua9a#ud0D
HA.2324@.TK2MSFTNGP09.phx.gbl>...[QUOTE]
> Have you tried to use only the REFERENCES permission on the table for the
> user creationg the SP? Check the "Owners and Permissions" topic in Books
> OnLine
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
ar
> chitec.chm::/8_ar_da_2s4z.htm).
> HTH,
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0401021018.12de3b15@.posting.google.com...|||Sorry, you are correct, I misread the original message. Unfortunately I
don't think it is possible to acheive what you want to achieve. I guess you
should take care who can create the procedures, so you can trust the person,
if you want this person to use the dbo user.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0401051432.6deec0fb@.posting.google.com...
quote:
> Thank you for the reposnse, but I think you missed the question. Your
> reponse is angled towards data access permissions. My question is
> about object creation permissions. I want a devloper to be able to
> CREATE and ALTER stored procedures in the dbo schema, but not be able
> to CREATE, ALTER, or DROP tables or other objects.
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...[QUOTE]
the[QUOTE]
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar[QUO
TE]|||I cam to the same conclusion thru my research. Will SQL Server Yukon
provide better permissions granularity to provide what I'm looking
for?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<#pcwG6C1D
HA.2224@.TK2MSFTNGP10.phx.gbl>...[QUOTE]
> Sorry, you are correct, I misread the original message. Unfortunately I
> don't think it is possible to acheive what you want to achieve. I guess yo
u
> should take care who can create the procedures, so you can trust the perso
n,
> if you want this person to use the dbo user.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0401051432.6deec0fb@.posting.google.com...
> message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...
> the
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books
\ar|||Does anyone have any other solutions to this? It seems like it should
be very easy to allow developers to create and alter sprocs under the
dbo schema, but not do any other DDL (such as DROP TABLE or DROP
PROCEDURE).
nospampedro@.yahoo.com (Peter Daniels) wrote in message news:<2fd8f155.0401081203.4e75bc46@.posting.
google.com>...[QUOTE]
> I cam to the same conclusion thru my research. Will SQL Server Yukon
> provide better permissions granularity to provide what I'm looking
> for?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:<#pcwG6C1DHA.2224@.TK2MSFTNGP10.phx.gbl>...
> message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...
> the
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books
\ar
No comments:
Post a Comment