How to write stored procedure for login insert and authentication as well as how assign permission to particuar user

Question

0.00/5 (No votes)

See more:

i have company name,company code ,username,password in my login table.but once i create one company and its username,password that company can access only its data which is in other table.like that i can create many companies.and each company can access its own data from other table. how to write stored procedure for that.

What I have tried:

this is my insert and authentication procedure
it is working fine.(showing output as 'successfully logged in')

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AuthLogin]
@Comapny_Name varchar(50) =null, 
@Company_Code varchar(50)=null,
@Usercode varchar(50) = NULL,
@Password varchar(50) = NULL,


@abc varchar(50)=null

As
BEGIN
IF	@abc='insert'
begin
set nocount on;
DECLARE @company_Id int





insert into login(Comapny_Name,Usercode,Password)
values(@Comapny_Name,@Usercode,@Password)
set @company_Id = SCOPE_IDENTITY();


end


if  @abc='select'
begin

if exists (select * from login  where Usercode=@Usercode and Password=@Password and Company_Code=@Company_Code)
begin
print 'login successful';
grant insert,update,select,delete on Company_Name where Company_Code=@Company_Code
end
else
print 'successfully logged in'
--select * from login_User
end
--DECLARE @MSG varchar(500)
end

Posted 23-Mar-16 3:27am

Member 12385326

Updated 23-Mar-16 3:51am

F-ES Sitecore

v2

Add a Solution

Comments

Richard Deeming 23-Mar-16 9:36am

Don't store passwords in plain text! You should only ever store a salted hash of the password, using a unique salt per record.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2016-03-23T03:51:00

Make sure your company table has an ID in it, so

SQL

ID | Name | Password
----------------------
1  | John | password
2  | Dave | 12345

You need to get the ID for the customer that is logged in, how you get that depends on your code. The easiest (but not the best) way is to store their ID in the Session when they login. You need to associate that company ID with any data relating to it, so your Employee table might be like

SQL

ID | CompanyID | Name
------------------
1  | 2 | Pete
2  | 1 | Ian
3  | 2 | Simon

This means that Pete and Simon work for company "Dave" and Ian works for "John". So when you want to list all employees your Employee_List stored proc needs to accept a @CompanyID parameter that your code will pass, and it will pass the ID of the currently logged in user.