how to send logged in username in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to send logged in username in stored procedure

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 10:56:43

hello

I want to know how to pass logged in username in the stored procedure.

Also how to check whether the user is guest or logged in??????

please help.

thank you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-29 : 11:11:29

what username? the one that is logged on to sql server or in your client app?
those are 2 different things.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 11:20:11

well I am performing a search function.In this search I have to implement user security.If the user is guest he will be able to get search result only from public docs,and if it is logged in then will get result from both public and docs created by him.

I know I need to get user name from the session ,and I dont know how to do that.Also I need to do checking whether the perticular person performing search is guest or logged in user.
I am using sql 2005 and Visual studio 2008 ,C#.

Also I have a question,should I pass username or userID????there can be users with same name???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-29 : 11:30:16

you pass in userId as paramtere and then filter by that in the where clause.
where's the problem?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 11:46:07

the problem is I dont know what the statement I should write in my C# code to pass user id to stored procedure???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-29 : 11:47:00

i'd guess SqlCommand.AddParameter

you'll have to show us some code for more...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 11:52:04

okay here is my code.

Actually I have a stored procedure,written by somebody else,where u enter userName and it will display all the docs of that person.

So I am just joining this stored procedure with my search procedure.

I am bit messed up with this.
the following code is not correct as its asking me to ""enter"" userName,but I want username from session.
Also I have not done any checking whether its guest or not.

Please help.

USE [charlotte]
GO
/****** Object: StoredProcedure [dbo].[sp_SystemSearch] Script Date: 08/29/2008 10:13:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SystemSearch]

@text as varchar(300),
@userName as varchar(64)
AS

BEGIN
SET NOCOUNT ON;

declare @srh as nvarchar(50)
SET @srh = 'FORMSOF(INFLECTIONAL,'+@text+')'

select * from (

SELECT s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
where Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

Union all
Select t.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",ElementText as "SearchElement"
FROM TextElement t,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where t.SystemElementID=s.SystemElementID AND Freetext(ElementText,@srh) AND t.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

UNION ALL

Select v.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",VideoMimeType as "SearchElement"
FROM VideoElement v,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where v.SystemElementID=s.SystemElementID AND Freetext(VideoMimeType,@srh) AND v.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

UNION ALL

Select tm.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",MetatagText as "SearchElement"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,@srh)AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

UNION ALL

Select b.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",BookmarkText as "SearchElement"
FROM Bookmark b,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where b.SystemElementID=s.SystemElementID AND Freetext(BookmarkText,@srh) AND b.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

) a inner join

(

SELECT DISTINCT a.[DocumentID],
a.[DocumentTitleText],
a.[CreatedUserName],
a.[CreatedDate],
a.[privilege],
a.[GroupName] FROM (
SELECT distinct MasterDocument.DocumentID,
MasterDocument.DocumentTitleText,
dbo.sf_getUsernameFromUserID(MasterDocument.CreatedByLID) CreatedUserName,
MasterDocument.CreatedDate,
[UserGroupRole].[Type] [privilege],
[UserGroup].[GroupName]
FROM MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
WHERE [User].[UserName] = @userName
GROUP BY [MasterDocument].[DocumentID], MasterDocument.DocumentTitleText, MasterDocument.CreatedByLID, MasterDocument.CreatedDate, [UserGroupRole].[Type]
, [UserGroup].[GroupName]
)a

LEFT JOIN (

SELECT distinct MasterDocument.DocumentID,
MasterDocument.DocumentTitleText,
dbo.sf_getUsernameFromUserID(MasterDocument.CreatedByLID) CreatedUserName,
MasterDocument.CreatedDate,
[UserGroupRole].[Type] [privilege]
,[UserGroup].[GroupName]
FROM MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
WHERE [User].[UserName] = @userName
GROUP BY [MasterDocument].[DocumentID], MasterDocument.DocumentTitleText, MasterDocument.CreatedByLID, MasterDocument.CreatedDate, [UserGroupRole].[Type]
, [UserGroup].[GroupName]) b ON a.DocumentID = b.DocumentID AND dbo.sf_convertDocTypeToValue (a.privilege) < dbo.sf_convertDocTypeToValue(b.privilege) WHERE b.DocumentID IS null

) b ON a.documentid = b.documentid

END

And here is my C# code
protected void btnSearch_Click(object sender, EventArgs e)
{
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@text", txtSearch.Text);

dboperations db = new dboperations();
DataSet ds = new DataSet();
Session["userName"];
ds = db.GetDataset("sp_SystemSearch", parameters);
dgItems.DataSource = ds;
dgItems.DataBind();
}

which is not complete as I dont know how to pass username

Thank You

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-29 : 12:00:16

parameters.Add("@userName", theUserNameGoesHere);

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 12:04:07

but I cannot enter user name during search.
See I have only one textbox and a button on the interface.
I want the username automatically when the user is logged in or if the user is guest.

so I need to do something with session

Thank for reply

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-29 : 12:05:36

well how are we supposed to know where you store your usernames?
take if from where you're storing it...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 12:11:31

I think I am not being able to explain you my point properly.
If I do parameters.Add("@userName", theUserNameGoesHere);
then my code will run,I dont deny that.

but for example think that u r logged in your hotmail.and when you perform seach to search one of your email,you dont have to put yr username to get result.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-29 : 12:19:32

Can you explain to us how we could possibly know where your application is storing the username if we cannot see any of your application's code?

EDIT: if you are using ASP.NET, maybe this will help you:

http://www.4guysfromrolla.com/webtech/110701-1.shtml

do more googling for "ASP.NET authentication" as necessary.

- Jeff
http://weblogs.sqlteam.com/JeffS

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 12:31:59

okay here are the tables for user management group
Table-masterDocument
columns-DocumentID,documentTitletext,

table-UserGroupRole
column-documentID,GrouID

table UserGroup
columns-GroupID,GroupName,GroupOwnerID,GroupIsDeletedBit,GroupIsOwnersPrimaryGroupBit,GroupDescriptionText

table-UserGroupMap
column-UserID,UserGroupID

table-User
column-UserID,UserName,UserPassword,UserIsDeleteBit,

table-UserProfile
column-UserID,UserFirstName,UserMiddleName,UserLastName

table-UserPreference
column-UserID,UserTheme,UserPageColur.

I hope this is what you want to know.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-29 : 16:23:25

That does not help us because it does not indicate where YOUR APPLICATION is keeping track of the currently logged in user.

Again, if this is an ASP.NET application read the link I sent you. And if that doesn't help, then I recommend hiring a good consultant to help you out because it sounds like you may be in over your head....

Good luck.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources