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 a login table question

Hey all- I have been reading this forum for quite some time now but I have never posted- this place is a great tool for young developers.

Anyway, I have a specific question.

I have a table (members)- with PK member_id- that holds member names and personal info etc.

My goal is for each member_id in the members table, insert that member_id into my fk column in a logins table. then, when each member_id is inserted, i want to insert the first initial of the member_name and first 4 characters of the last_name, along with the member_id into the logins table in the login_name column.

i also want to insert a unique 6 digit string into the logins table (login_password). I'm not great with script, so any help would be appreciated.

Thanks,

sudeep.Suppose you have this situation:

CREATE TABLE members
(member_id NUMBER(2) CONSTRAINT pk_mem PRIMARY KEY,
member_name VARCHAR2(10),
last_name VARCHAR2(10)
);

CREATE TABLE logins
(member_id NUMBER(2) CONSTRAINT fk_log_mem
REFERENCES members (member_id)
INITIALLY DEFERRED DEFERRABLE,
login_name VARCHAR2(20),
login_password VARCHAR2(6)
);

CREATE SEQUENCE seq_mem_login START WITH 100000;
/* The sequence is used to generate unique login passwords in this example */

CREATE OR REPLACE TRIGGER trg_member
AFTER INSERT
ON members
FOR EACH ROW
BEGIN
INSERT INTO logins
(member_id,
login_name,
login_password
)
VALUES (:NEW.member_id,
SUBSTR (:NEW.member_name, 1, 1)
|| SUBSTR (:NEW.last_name, 1, 4)
|| TO_CHAR (:NEW.member_id),
seq_mem_login.NEXTVAL
);
END;
/

insert into members values (1, 'john', 'smith');

select * from members;
MEMBER_ID MEMBER_NAM LAST_NAME
---- ---- ----
5 john smith

select * from logins;
MEMBER_ID LOGIN_NAME LOGIN_
---- ------- --
5 jsmit5 100000|||thanks man- i'll let you know how it goes in a few.|||whats wrong with this code? anyone?

it gives me the following error:

Server: Msg 128, Level 15, State 1, Procedure trigPassword_CreateMember, Line 9
The name 'member_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

--Create the trigger
CREATE TRIGGER trigPassword_CreateMember ON Accounts
AFTER Insert
AS
BEGIN
BEGIN TRAN
INSERT INTO Logins(member_id, login_name, login_password)
VALUES (inserted.member_id, SUBSTRING(inserted.first_name,1,1) + SUBSTRING(last_name,1,1) + CONVERT(char(5),inserted.member_id),
inserted.account_id)
COMMIT TRAN
END
GO

CR / VB Sort Problem

OS= Windows XP SP2 Professional, Crystal Report XI Developers Edition, Visual Basic 6 Enterprise

Below is the code I am trying to use to print a report. This code except for this "ElseIf" statement is used and works fine to print other reports. The only difference between printing this report and the other reports is 1) the name of the report and 2) this is the only report I have a sortfield.
.
.
.
ElseIf ReportName = "Alternate.rpt" Then
Set craxreport = craxapp.OpenReport(ReportPath & "\" & ReportName)
Set dbTable = craxreport.Database.Tables(1)
craxreport.RecordSortFields(0) = "+{AlternatePart.dbEnterDate}"
End If
craxreport.DiscardSavedData
' ****************************************************************************************
CrystalActiveXReportViewer1.Refresh
Call CrystalActiveXReportViewer1_RefreshButtonClicked(True)
CrystalActiveXReportViewer1.ReportSource = craxreport
CrystalActiveXReportViewer1.ViewReport
Set craxreport = Nothing
When I run this code, if I keep "craxreport.RecordSortFields(0) " I get a "Subscript out of Range" error.

The report is defined as follows:
- It uses a table called "AlternatePart"
- There are 3 fields in the table - (dbOldPn, dbNewPn, and dbEnterDate)
- All fields are described at TEXT
- The report shows all 3 fields.
- I used the Record Sort Expert to set up one sort field and that is "AlternatePart.dbEnterDate"

Can you tell me what is possibly causing the above error?

ThanksI have a little experience with crax.
I think maybe Indexes are not zero based. try index 1|||Yes try using craxreport.RecordSortFields(1) and see if it is working|||Yes try using craxreport.RecordSortFields(1) and see if it is working
That was the problem -- thank you.

Sam