Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)
The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"??" wrote:

> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which role
> let the login make ddl changes only in on schema (not in the dbo schema)
|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "??" wrote:
|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
[vbcol=seagreen]
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/update
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:

Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"×?×?" wrote:
> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which role
> let the login make ddl changes only in on schema (not in the dbo schema)|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "×?×?" wrote:
> > I want to let my developers to create/alter stored procedure only under a
> > specific schema. which role let the login create a procedure , or Which role
> > let the login make ddl changes only in on schema (not in the dbo schema)|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/update
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:
> > The best way is to create a custum role with the permissions you need: no
> > built-in database role has such a permission design.
> > So, for example:
> >
> > USE MyDatabase
> > CREATE ROLE MyNewRole
> >
> > ... add members to this new role:
> >
> > EXEC sp_addrolemember 'MyNewRole', 'User1'
> > EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> >
> > then assign permissions:
> >
> > GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> > TO MyNewRole
> > ...etc.
> >
> > You'll find detailed explanations in BOL at the topics dedicated to the
> > different statements i've showed here
> > Gilberto Zampatti
> > "×?×?" wrote:
> >
> > > I want to let my developers to create/alter stored procedure only under a
> > > specific schema. which role let the login create a procedure , or Which role
> > > let the login make ddl changes only in on schema (not in the dbo schema)

Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"??" wrote:

> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which ro
le
> let the login make ddl changes only in on schema (not in the dbo schema)|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "??" wrote:
>|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
[vbcol=seagreen]
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/upda
te
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:
>sql

create / alter a store procedure

hello,
i want to create a stored procedure which i can use to create / alter a
other stored procedure.
The store procedure should have 2 parameters name and content of the stored
procedure
I think this could be made with dynamic sql...
Any ideas how to do this.
I want to use the this to alter stored procedures from inside asp.net
calling the new procedures with the adequate parameters:
thanksHi
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
Why? Don't you know a name of SP nor parameter's name?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||hi Xavier,
are you think over the possibility to use templates from QA?
regards,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> Hi
>
> Why? Don't you know a name of SP nor parameter's name?
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
>
>|||i try to explain better
If i want to create a new SP -
Name:SalesReport
SPContent: is the string value of a tested StoredProcedures in the query
analyser
example
CREATE PROCEDURE MyNewSP
--@.ParSlotid int
AS
Declare @.PSlot int
Declare @.actionNr int
Declare @.slotid int
Declare @.actionId int
DECLARE @.StartDate Varchar(10)
DECLARE @.EndDate Varchar(10)
DECLARE @.NrRows int
set @.actionNr=1254
SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
.....
After i know the name and the content of the SP i want to call al a SP
example
exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that the
'SalesReport 'sp is created
My question is what must be the content of "CreateNewSP " to realize this
thanks
"Xavier" wrote:

> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the store
d
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||Xavier
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
>
What version are using?
The above statement you cannot use in SQL Server 2000 and you are about to
replace it with dynamic sql , I'm sure you are aware ofsome drawbacks using
dymanic sql in the production enviroment.
I don't know your business requiremnts ,so I make a guess , why not to
have a stored proedure for reporting? I mean , why do you want to supply a
DDL for stored procedure as a parameter? Or perhpas I did not understand
you, did I?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||Rather than doing this from your ASP.NET application directly by connecting
to SQL Server, use SQLDMO. This provides a decent object model over the
database and you can use it to create/modify and delete objects of various
types. Here is an article that should help you:
http://www.sqljunkies.com/Article/1...BCA1D8C97D.scuk
By using SQLDMO you avoid creating a procedure in the database to churn out
other procedures. This way your solution can move easily between databases
if need be.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||No vull tocar el collons però segueixo sense entendre-ho
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Xavier" wrote:
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that t
he
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||hello SriSamp,
exactly that i want to do for administrative scopes...
Because on my IIS - server there is no instance of SQL - i can not use
SQLDMO?!
So my idea was to create a SP - which creates new SPs
thanks
"SriSamp" wrote:

> Rather than doing this from your ASP.NET application directly by connectin
g
> to SQL Server, use SQLDMO. This provides a decent object model over the
> database and you can use it to create/modify and delete objects of various
> types. Here is an article that should help you:
> http://www.sqljunkies.com/Article/1...srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
>
>|||
> What version are using?
i use SQL2k amd ASP.Net on a separat server
>you are about to replace it with dynamic sql
but how looks the SP which can use dynamic sql - have you a simple example
> why not to have a stored proedure for reporting?
because this SP are - not very complex and only valid for 2-6 ws - and
maintened by some user who did not have access over QA - but only over
Web-frontend with impersonation.
thanks|||have you any example how can this be done?
thanks
"Enric" wrote:
> hi Xavier,
> are you think over the possibility to use templates from QA?
> regards,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Uri Dimant" wrote:
>