Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have AD ID users on a SQL 2008 server that can't edit their own jobs. They can create them, but in order to edit them, they have to delete them and then re-add them. The issue is explained here: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/08/owner-of-the-job-will-not-be-able-to-modify-edit-the-job-in-sql-server.aspx

I did further research and figured out in my particular case it's because I added AD ID users using the CREATE LOGIN [domain\user] syntax. Using that method, the case of the AD ID is what you specify in the brackets.

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

Furthermore, I was using this script to get the correct casing for a particular user:

execute as login = 'usa\johndoe'
SELECT SUSER_SNAME()
revert;

But it was returning all lowercase for me, yet all uppercase when I asked the user to run SELECT SUSER_SNAME() and give me the result. The correct is all uppercase (well, in order for that user to be able to edit jobs).

I'm at a loss and very frustrated.

-- I've found the best (only?) way to get the correct case is using the SQL interface to add a user, clicking search, typing the AD ID, then clicking check names. For the example above, it returns all uppercase, despite that script example returning all lowercase.

share|improve this question

migrated from stackoverflow.com Feb 3 '12 at 22:46

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

First a few assumptions:

  • "Cases" is referring to upper- or lowercase-ness
  • Login = whole instance principal
  • User = individual database principal

That being the case, the answer to your question is "no" without using SQL CLRs. While you can automate database user creation by querying sys.server_principals, if your source data is incorrectly cased, then so will your subsequent data. This is the GIGO principle.

The only solution I can think of to meet your needs would require you to query AD directly, which would require the use of some SQL CLRs. Fortunately, a lot of the code has been written and can be repurposed from the article, How to avoid 1000 rows limitation when querying active directory (AD) from SQL 2005 with using custom code.

Unfortunately this potentially requires you to specify a full LDAP path, which may be less than desirable. If you can figure out a way to return the authoritative data based on a case-insensitive account search, you can pass that data back into SQL via CLR and automate user creation or fixing.

share|improve this answer
add comment

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

Built in, you already found it -- use the directory searching interface in the GUI. Alternatively, you could use SQL CLR (as mentioned), or write a PowerShell script. You could certainly write a routine to clean up an instance using either method.

Note that the GUI rewrites the domain name, even if it's incorrectly cased when entered, but not the login name itself. (You can see this by manually typing something in, then hitting the Script button.)

But it was returning all lowercase for me, yet all uppercase when I asked the user to run SELECT SUSER_SNAME() and give me the result. The correct is all uppercase (...).

Under impersonation, SUSER_SNAME() returns the principal name as entered. If you try the test again with a different casing, it will return the exact name you typed in. The only reason the impersonation works using an incorrectly-cased name is because the Windows collation says the comparison of the two is a match.

Not under impersonation, there are two cases: (a) group login: the principal name is returned from what Windows supplies, which is always correctly-cased, and (b) individual login: the principal name is returned from what is entered for the name in SQL Server (it doesn't go out to Windows to double-check).


Now, having said all that, try as I might, I cannot replicate this problem using 2008 R2 management tools. If you're still running 2008 (non-R2) management tools, one option would be to try upgrading the tools, because this appears to be a bug in Management Studio itself.

share|improve this answer
    
Thanks for the answer. We are still using original release (not R2 for both our SQL Servers and Management tools). Do you think if users upgraded their management tools they'd be able to edit jobs regardless of the casing of their login? –  Brad Aug 2 '12 at 16:55
1  
@Brad: According to my tests, yes, I believe upgrading the tools would fix the problem. (Obviously, try it with one user before getting everyone to do it.) I always recommend running the latest management tools regardless of server version anyway, and this is a really safe upgrade to do. –  Jon Seigel Aug 2 '12 at 17:00
    
Will try and let you know... –  Brad Aug 2 '12 at 17:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.