October 12, 2010 at 7:16 am
Has anyone ever had the issue of truncation of username in the resultset of sp_helpuser? I've been trying to create my own version of sp_helpuser to see if I can get around the truncation issue.
The issue basically involves the username, which is comprised of our domain name which is 16 characters in length, including the slash, and first initial, lastname. Example: DomainName12345\usernameABCD
When I run sp_helpuser, all columns are output and return all user related info correctly:
Disregarding other columns returned: DefDBName DefSchemaName UserID SID
UserName GroupName LoginName
DomainName12345\usernameABCD db_datareader DomainName1234\usernameABCD
The Domain name is 16 characters long and the username is 12 characters long for total of 28.
Now run sp_helpuser 'db_datareader' ....... By design, only 4 columns return.
Notice truncation of the username when it show up in the column Users_in_group. The column is only capable of allowing 25 characters. (DomainName12345\ = 16, and the username = 9). So usernameABCD which show fine in sp_helpuser, is now reduced to usernameA.
GroupName GroupId Users_in_group UserID
db_datareader 16384 DomainName12345\usernameA 19
Not sure where this is handled in the proc, but once I saw this difference I'm curious as to the "why".
cheers, bph
BPH
October 12, 2010 at 8:06 am
By the column names you provided I'm guessing you are working from SQL Server 2000 (not SQL 2005, which is the forum you posted in). I received different column names when executing this against a SQL 2005 instance.
According to BoL the sysname is "functionaly equivalent to nvarchar(128)" which is the field used sp_helpuser and sp_helpgroup. Which in earlier versions it was restricted to 30 characters I think.
If you pass a group name to sp_helpuser it actually goes through and executes it against sp_helpgroup. Since you are in SQL 2000 you can actually view the code behind this procedure.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 12, 2010 at 8:12 am
I'm working on SQL 2005 SP3 servers. Based on what you wrote I may have more concerns than I thought.
What columns do you get when you run sp_helpuser and sp_helpuser 'db_dtareader'?
BPH
October 12, 2010 at 10:42 am
Well I apparently missed typed, I get the same columns.
I already closed my session so I'm not to sure what I typed to get something that looked different 😀
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 12, 2010 at 10:50 am
This is the query I pulled from the sp_helpgroup sproc that is executed when you pass a group name to sp_helpuser. I believe the substring is your culprit.
select Group_name = substring(g.name, 1, 25), Group_id = g.principal_id,
Users_in_group = substring(u.name, 1, 25), Userid = u.principal_id
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.name = @grpname
and g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 12, 2010 at 11:49 am
Hi Shawn,
That's the problem right there. Don't know how many times I looked at sp_helpuser last night, and never saw sp_helpgroup right there under my nose.
I created two new procs based on the sp_helpuser and sp_helpgroup, and put them in my package which hits all the servers. Run's perfect, delivers the entire username.
Thanks for the help.
Cheers, bph
BPH
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply