Jeff Moden (11/6/2014)
But it still causes an issue when trying to use them in their full 21+ character format when adding to SQL services and when adding to SQL security...right? That is at least according to my testing.
I wouldn't have thought so but I don't know because I don't make it a habit of using long names for user or service names.
What testing have you done? I wouldn't mind giving it a try myself.
It only happens with AD accounts, not SQL Native accounts. You can create a SQL Native account up to 115 characters. However, when you create an AD account, it is limited to 20 characters after the '\' character. So if try to create a login of "Domain\ABCDEFGHIJKLMNOPQRSTUVWXYZ"...you only get "Domain\ABCDEFGHIJKLMNOPQRST" instead of the entire login. It allows you to create it, but when you look at it after creation...if you try to query it or if you try to add it to the SQL services, it will only verify to the "T"...if you enter the entire login to the "Z" on the service account, it will fail.
The reason we create longer logins is simply for the ability to create descriptive AD Security Groups that users can be added to in AD and the AD group added to the server/database. This helps in environments with thousands of users and an access control team with a standardized policy for adding users to the groups to gain specifically defined roles (read/write/execute) in the database without having to engage the DBA team.
Microsoft\SQL_ServerA_Northwind_Read (26 characters after the "\", won't work)
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.