SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SUSER_SNAME not giving updated name in AD
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  02:45:30  Show Profile  Reply with Quote
Hello techies,
In my application i am using suser_sname sql function which returns current login name. Initially a user named "Jane christopher" was the name present in AD. She was able to login in her machine and launch my application. After few days her name in AD got renamed to "Jane john". When she logged into the her machine and launch my application, the suser_sname sql function in my application is returning the old name "Jane christopher". Can anyone help me on how suser_sname gets the current login name? From where does the suser_sname gets the login name? I tried restarting sql service but that did't work. So please get me some deep technical reason for this?

Maddy

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  03:10:19  Show Profile  Reply with Quote
whats the sql login existing for same user? look beneath server->security->logins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  03:55:24  Show Profile  Reply with Quote
Where to check for the security logins? Do i need to look from management studio? or is there somewhere else i need to look in?

Maddy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  03:57:27  Show Profile  Reply with Quote
yes. its from management studio. connect to your server, expand server->security->logins



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  04:06:59  Show Profile  Reply with Quote
Ya there i see users as
1. A2SYMMD06117\SQLServer2005MSSQLUser$A2SYMMD06117$SQLEXPRESS
where A2SYMMD06117 is my machine name that is sql server machine
name
2. BUILTIN\Administrators
3. BUILTIN\Users
4. NT AUTHORITY\SYSTEM
5. sa

Do u mean whoever connects session to the sql server his/her name will be under this server->security->Login?



Maddy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  04:14:48  Show Profile  Reply with Quote
its should be there ideally. Or he should be under adminstrator of machine so that it will get automatically mapped using BUILTIN\Administrators role

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  04:25:24  Show Profile  Reply with Quote
Basically my application is server client application. Client app can run in any user login. In that process i connect to sql server using new name. So exactly what process goes on internally can u brief me on this? And i want some detail on SUSER_SNAME() in sql. From where does this function get the current security context name?

Please help me out i am struck in a weird problem.. Please help

Maddy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  04:35:36  Show Profile  Reply with Quote
start sql profiler on background while you run application and you will be able to capture login details it uses to logon to sql server.
SUSER_SNAME() takes logins details from sql server corresponding to user/process in scope who logged in for executing code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  04:40:56  Show Profile  Reply with Quote
what do u mean by sql profiler? The problem here is the SUSER_SNAME() itself returning old login name instead of new login name. So do u have idea on where this function present in SQL DB. In which DB can i get to see this function? Is there a way to look the definition of this function?

Maddy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  05:00:19  Show Profile  Reply with Quote
SUSER_SNAME() is standard sql function which is present inside every database under programmability->functions->system functions-> security functions.
I think your problem has nothing to do with this function but its problem of not refreshing mapped username used inside SQL after AD refresh

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  05:05:42  Show Profile  Reply with Quote
How to check for the mapped username used inside SQL? Is each user name mapped inside the SQL? What does the SQL does with these mapped names? Are these names mapped from the AD?(is the username mapped in sql is same which we use to login into our system). Can u brief on mapping usernames inside sql?

Maddy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  05:18:00  Show Profile  Reply with Quote
thats what i told earlier
ie connect to your server, expand server->security->logins

Each username are mapped in sql server either directly from AD or by creating a new login seperately in sql. By default they both will be same if done from AD but you can change the name at sql server if you want.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  05:38:43  Show Profile  Reply with Quote
But previously i did't see this user mapped in security->logins in Managmt studio still my application was able to connect to DB from user login in client machine. I think there is something else need to be done after renaming the user in AD. Can you please help me in finding out whats wrong? Is there any quick steps to figure out the problem?

Maddy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2011 :  06:11:41  Show Profile  Reply with Quote
quote:
Originally posted by mandarapte409

But previously i did't see this user mapped in security->logins in Managmt studio still my application was able to connect to DB from user login in client machine. I think there is something else need to be done after renaming the user in AD. Can you please help me in finding out whats wrong? Is there any quick steps to figure out the problem?

Maddy


that means its mapped directly from AD and user is part of buildin users or administrators group.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mandarapte409
Starting Member

India
11 Posts

Posted - 12/05/2011 :  06:47:49  Show Profile  Reply with Quote
So where does that BuiltIn/users group will be? will it be in AD? If its in AD where it will be? How can i get to see the users under Builtin/users group in SQL? Or builtin/Users group is same as users group in AD?

Maddy
Go to Top of Page

philipjbernardi
Starting Member

USA
1 Posts

Posted - 01/09/2013 :  15:38:42  Show Profile  Reply with Quote
I think the OP was asking what system catalog (table or view) is used to supply the value that comes from the SUSER_SNAME() function. I personally believe that the value returned in SQL2005 is diffenent than from SQL2008 (all things being equal). I believe that this function is returning a WINDOWS LOGIN type under SQL2008 where it was returning a WINDOWS GROUP type under SQL2005.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000