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 new user

hi

im unable to create a new sql server authentication login

It is appreciated if anyone helps me

thanks in advance

It seems as though your login does not have permission to create a new login.

Log in as the administrator, and give your login the appropriate priviledges.

|||

What does "unable" eman in your case, you did not specify if you are getting an error or the login is just not working ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hi jens,

thanks for your reply

I logged in with windows authentication and tried to create a new login for sql server authentication..it didnt work

then i logged in windows mode and tried to create tables...that also didnt work....then only i got the solution that i have to install SP 2 and while installing i can specify the authentication as windows (or) sql server (or) both....

so i installed and specified the authentication as mixed and it is working fine now

Thanks,

Poorani

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

Create a database login in the default domain

I'm trying to create a login in the default domain. I know I can pull
this information from xp_loginconfig, but don't see how I can use it in
the context of sp_grantlogin.

For example, pull the domain the user is currently logged in on and
insert it into the sp_grantlogin script. Has anyone ever done this in
the past?"Aaron" <aaronakzin@.yahoo.com> wrote in message
news:1104954562.282980.203900@.z14g2000cwz.googlegr oups.com...
> I'm trying to create a login in the default domain. I know I can pull
> this information from xp_loginconfig, but don't see how I can use it in
> the context of sp_grantlogin.
> For example, pull the domain the user is currently logged in on and
> insert it into the sp_grantlogin script. Has anyone ever done this in
> the past?

First of all, I strongly suggest that you consider adding domain accounts to
a domain group, and then just grant login to the group - that's much easier
to manage (and it's the MS recommended way to implement security).

I'm not sure I really understood your question - do you mean that given the
Windows domain account name 'Someone', you want to find the default domain
name from xp_loginconfig (let's call it SOMEDOMAIN), and then grant login to
'SOMEDOMAIN\Someone'? If so, see the (untested) code below.

If I misunderstood you, you might want to give a specific example of what
you're trying to do.

Simon

create proc dbo.GrantLogin
@.Account sysname
as
begin
declare @.Domain sysname,
@.Login sysname

create table #t
(Attrib sysname, Val sysname)

insert into #t exec master..xp_loginconfig

select @.Domain = Val
from #t
where Attrib = 'default domain'

set @.Login = @.Domain + '\' + @.Account

exec sp_grantlogin @.Login
end|||Great, this seems perfect. Thanks for your help.

Create a custom login.

Hi all,

I need an user to do the following tasks. What kind of login I could provide him and how do I give him rights to do them without providing hin the SA (sysadmin role) ?

Create Login
Create Database
Create Tables ( with Pks, Fks & Indexes)
Create Stored procedures
Create Functions ( table valued / scalar valued)
Insert Master data
Create Jobs
Execute Job for first time
Populate sample

Thanks in advance,

DBAnalyst

Refer to books online about database fixed roles for the steps you have asked such as:

1) db_accessadmin

2,3,4,5,6 & 7) db_owner

Create 3 users tables

Hello,
In my web site I have 2 types of users: students and professors.
All users need to login but the personal information for each user type
has different fields.
So I create 3 tables: professors, students and authentication,
"professors" will have all the personal data from professors.
"students" will have all the personal data from students.
authentication will have the username and password from all users.
How can i link authentication table to both professors and students
tables so when the user logins i get the data from that user, either a
professor or student?
And is the right way to do this?
I am using SQL 2005 and Asp.Net 2.0
Thanks,
MiguelOn Wed, 21 Dec 2005 23:10:00 +0000, Miguel Dias Moura wrote:

>Hello,
>In my web site I have 2 types of users: students and professors.
>All users need to login but the personal information for each user type
>has different fields.
>So I create 3 tables: professors, students and authentication,
>"professors" will have all the personal data from professors.
>"students" will have all the personal data from students.
>authentication will have the username and password from all users.
>How can i link authentication table to both professors and students
>tables so when the user logins i get the data from that user, either a
>professor or student?
>And is the right way to do this?
>I am using SQL 2005 and Asp.Net 2.0
>Thanks,
>Miguel
Hi Miguel,
I would use one Persons table to hold information that applies to both
professors and students. Specific information then goes in the students
and the professors tables.
Login information could go in the Persons table, but is probably better
placed in a seperate table (for reasons of security, but also for
flexibility - maybe, someone will someday need two seperate accounts,
then you'll be ready for it).
The tables Persons, Students and Professors all have the same primary
key; this same column is also foreign key in the Students and Professors
table, in both cases referencing the Persons table.
The Logins table has userid as primary key (of course) and has also a
foreign key that referenced the Persons table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Couple Issues Relating to SQL Server Security/Login

Hello,
Is it possible to tell SQL Server Express to only use SQL Authentication? I
would like it so that there is just an sa SQL account on a SQL Server
instance. Is it possible to script the SQL Server Express setup to do just
that? I certainly understand that it can be more secure to use Windows
Security over SQL Authentication, however the password is very strong (64
characters in length randomized).
Sincerely,
James Simpson
Straightway Technologies Inc.There are only two authentication modes to login to SQL Server.
1- Windows (only) Authentication
2- Mixed Authentication
There is no 3- SQL (only) Authentication.
However, you can delete Windows Logins from the Security node in SSMS, so,
only users who has SQL Login accounts will be able to login to your SQL
Server. However this is a little bit risky as you'll have only one sysadmin
("sa" in your case) and if you somehow lose its password or something then
you'll be locked yourself and can not get in to your SQL Server. You must be
very very careful about this operation.
Ekrem ?nsoy
"James Simpson" <JamesSimpson@.discussions.microsoft.com> wrote in message
news:481D3155-0061-46F2-912C-8003D6C4A950@.microsoft.com...
> Hello,
> Is it possible to tell SQL Server Express to only use SQL Authentication?
> I
> would like it so that there is just an sa SQL account on a SQL Server
> instance. Is it possible to script the SQL Server Express setup to do
> just
> that? I certainly understand that it can be more secure to use Windows
> Security over SQL Authentication, however the password is very strong (64
> characters in length randomized).
> Sincerely,
> James Simpson
> Straightway Technologies Inc.|||Fair enough. I am not worried about losing the password per se since it wil
l
be an application that will be logging into SQL Server for me.
Sincerely,
James Simpson
Straightway Technologies Inc.

counting field totals as a formula

Hi,

I'm trying to count field login totals for users which updates another field
in another table. Which is the most efficient method?

I don't want to use a standard query as it will take too long if there are
1000 users per company each with 1000 plus logins.

I was thinking in terms of either a function, or a formula (using the built-
in formula field within mssql).

the query though (as its the only way which i'm familiar) is:

SELECT SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
WHERE (CompanyName = x) AND (EmployerID = y)

how would i write this as a formula or as a function?James M via SQLMonster.com (forum@.SQLMonster.com) writes:
> I'm trying to count field login totals for users which updates another
> field in another table. Which is the most efficient method?
> I don't want to use a standard query as it will take too long if there are
> 1000 users per company each with 1000 plus logins.

Depends on what you mean with a "standard query", but if you mean one
that is ANSI-compliant you are likely to be right.

> I was thinking in terms of either a function, or a formula (using the
> built- in formula field within mssql).

Ehum, there is no "build-in formula field" in SQL Server. There are
computed columns, which may appear as "formula fields" in Enterprise
Manager.

Anyway, I don't think a computed column would be a good idea, since that
would have to be a scalar UDF, and they are not known for being
performance boosters. You could index the column, but there is some
hassle with this.

Anyway, this query should take you a long way:

UPDATE tbl
SET logintotals = e.TotalLogons
FROM tbl t
JOIN (SELECT CompanyName, EmployerID,
SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
GROUP BY CompanyName, EmployerID) AS e
ON t.CompanyName = e.CompanyName
AND t.EmployeeID = e.EmployeeID

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp