Clicky

I renamed an userlogin from 'Mike' to 'Mike2" in the active directory. When I run SUSER_SNAME() from a machine logged in as 'Mike2', the server still returns 'Mike' instead of 'Mike2'. But, after a restart of the server, I get 'Mike2'. Similar issue has been report here:

http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/50561904-ee03-45ed-a473-e5b95a155264

Please note that running "select suser_sname( suser_sid() )", i.e. even when I pass the the sid, I still get the old name before restart.

Request experts to suggest ways to fix this issue without restarting the server.

asked 11/28/2011 02:13

sukhoi35's gravatar image

sukhoi35 ♦♦


6 Answers:
Restart the SQL service?
link

answered

aarontomosky's gravatar image

aarontomosky

Did you tried DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')?
link

answered 2011-11-28 at 22:27:02

anujnb's gravatar image

anujnb

Since many users will be using the SQL server, I will restricted from restarting the service.

When I run the DBCC command, I get the error:

Msg 2571, Level 14, State 11, Line 1
User 'guest' does not have permission to run DBCC freesystemcache.


Any suggestions?
link

answered 2011-11-28 at 22:44:28

sukhoi35's gravatar image

sukhoi35

To run this command, the user needs ALTER SERVER STATE Permission, try using other Administrative logins.  Usually Guest accounts will not have the permission to run DBCC Command.
link

answered 2011-11-29 at 06:09:36

anujnb's gravatar image

anujnb

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore' did not work.

The following SQL Server 2000 article suggests that by design, the old user-name is retained:

http://technet.microsoft.com/en-us/library/cc966454.aspx


Renaming Windows User or Group Accounts
With SQL Server 2000, you can grant Windows users and groups permissions to access objects in the database directly. In that case, the SID and Windows user or group names are stored in the sysusers table.
When the Windows administrator renames the Windows group or user, the name change is not propagated to SQL Server 2000. It is important to understand the reasons for this.
In SQL Server 2000, as with earlier versions, administrators and developers are writing numerous stored procedures, Transact-SQL scripts, triggers, and so on. Assume that Susie Jones is a user who creates a table in the database. Her login name is SUSIEJ, and her table is named SUSIEJ.SALESDEMO. Susie grants permissions for others to access her table, and several of her colleagues create views and stored procedures based on her table. When Susie gets married to Bob Taylor, her username is renamed to SUSIET. If SQL Server 2000 were to pick up the change, her table would suddenly be SUSIET.SALESDEMO, which is a completely different object. The views, stored procedures, and any code that was written to access this table would break. This is why SQL Server 2000 does not automatically rename user accounts when the Windows account in the Windows User Directory is renamed.

*********** ---- ***************

Renaming Windows User or Group Accounts
When a Windows user or group is renamed using the User Manager for Domains tool in Windows NT 4.0 or the Active Directory Users utility, SQL Server 2000 is unaware of that change. SQL Server 2000 maintains the fully qualified name of the user or group in the sysxlogins table for performance reasons, as it can be very slow to query the domain controller for this information. This is true when many name lookups are done or the domain controller is connected over a slow WAN link.
The fact that the names of SQL Server 2000 users and groups may differ from those of Windows users and groups does not cause any security problems. The permissions set for the user or the group continue to function correctly, as SQL Server relies only on the SIDs internally.
When the SUSER_SNAME() and SUSER_SID() functions are used to return the login name and SID of the user, respectively, they first query the sysxlogins table. The Windows Local Security Authority (LSA) is queried only if the sysxlogins table does not contain the username or SID.
Another effect of using these functions is that the usernames in system messages may not report an up-to-date name.
Views of the sysxlogins System Table
The sysxlogins system table contains information about logins of users. This system table, which exists only in the master database, should be accessed only through these views:
•      Syslogins — provides information on SQL Server logins, and interprets the status column so that it can be understood more readily.
•      sysremotelogins — contains one row for each remote user allowed to call remote stored procedures on SQL Server.
•      sysoledbusers — contains one row for each user and password mapping for the specified linked server.
link

answered 2011-11-29 at 19:40:18

sukhoi35's gravatar image

sukhoi35

The above comment gives a clear picture of the use of suser_sname()

link

answered 2011-12-12 at 03:13:49

sukhoi35's gravatar image

sukhoi35

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 11/28/2011 02:13

Seen: 770 times

Last updated: 12/17/2011 05:18