June 23, 2010 at 7:36 am
Hi,
Is there anyway to stop non sysadmin users from using SET LANGUAGE <language>?
Thanks,
Jinx.
June 24, 2010 at 8:22 am
I'm no longer at work, so can't do any testing, but. . . can you setup a trigger on "set langauge" then have a case statement that checks the current login-role and either executes set language or returns "No" ??
June 24, 2010 at 8:29 am
Hmmm, I could try that out.
I was hoping for some sort of setting in Policy management.
Thanks 🙂
June 24, 2010 at 2:47 pm
Just being curious: What's the reason to disallow a change of language settings? Care to share an example? If it's due to using DATEPART(week/weekday) or @@DATEFIRST we might be able to show you alternative that will work regardless of the language settings.
June 25, 2010 at 1:07 am
It's a problem with DATETIME and SMALLDATTIME in fact. Since they are not language independent and do not 'support' the format YYYY-MM-DD when the language setting is British. Yes I know there are several ways around this by explicitly specifying the format, etc etc, and that using this format in apps is 'cowboy' programming at best, but (in the real world :p) we have several ancient legacy applications that cannot be changed, that insert into the database in that format. Of course the insert fails when the accounts the apps use are set as anything other than US_English.
We upgraded from SQL 2000 to 2008 and set all language to a standard British, and in the process of testing these changes, 'broke' these legacy applications. The ideal solution would have been to change the data type to the new DATETIME2, but unfortunately Webfocus, our BI tool does not yet support the new SQL 2008 data types so they cannot be used.
Since we are in the UK ^^ I need most logins to have the British language setting but just the odd login needs to STAY us_english, else things will fall over.
Some of our database users have the ability to tinker (!) and I would like to lock this down so they cannot change anything, or if they do I'm am at least aware of the change.
Jinx.
June 25, 2010 at 1:39 am
If you're able to remove the dashes prior or during insert it will work independend of any language setting, since SQL Server will identify '20100625' as YYYMMDD.
Could you describe a scenario where the SET LANGUAGE modification will cause trouble? I'm a little confused since you mentioned 3rd party apps (that hopefully will use separate logins and therefore should not be influenced by any language modifying commands) vs. users that will use SET LANGUAGE (which will only affect the DATEFORMAT setting under some circumstances within the related batch). I don't see how those two separate issues are related.
Unless...
We're not talking about using SET LANGUAGE but rather ALTER LOGIN. If that's the case, you need to limit the permissions for those users. There is no reason to permit alter login to any user role anyway.
June 25, 2010 at 2:22 am
This is a hard one to explain, but a long story short...the apps cannot be changed because the users who are supposed to know how to change them, don't or can't be bothered (they are not 3rd party but old apps written by people long gone). Many of these apps generate Insert statements that the users can use. This group of users can also connect to their database using the login that the apps use, or will (for testing) use their account details within the apps (they also request new accounts etc). Since I am on the database side I have no say in or control of what that team does or does not do, but am responsible if anything goes wrong with the data. Last week, someone, somewhere along the line changed a language setting and suddenly during testing none of the inserts would work for a particular login - the one they were connected with and using to execute the generated insert. Either the data would not insert, or in the worst case, it was inserted with the month and day swapped in cases the day could be a valid month (eeek!) major disaster if this were to ever happen in a production system.
Does this make sense?
I did suggest a change to the way the apps generate the inserts (by as you suggested removing the '-'), but they just stared at me blankly...clicked around a bit and seemed completely lost.:-P
Jinx.
June 25, 2010 at 3:55 am
Jinx-640161 (6/25/2010)
...Does this make sense?
I did suggest a change to the way the apps generate the inserts (by as you suggested removing the '-'), but they just stared at me blankly...clicked around a bit and seemed completely lost.:-P
Jinx.
Your explanation made sense but left a bad feeling in my stomach... :pinch:
As far as I know, you cannot add policies to control the SET permissions for language and dateformat.
I'd recommend you get a consultant in to do a detailed analysis of the current scenario and ways to resolve it. Maybe the '-' issue can be resolved, too (eliminating the root cause of the pain). Maybe INSTEAD OF DML triggers are an option, too. Hard to tell from here.
Please note, the language and dateformat settings can easily get complicated... Just to control the language setting won't help.
Example:Try to predict the output of the following script:
-- create test table
CREATE TABLE #dummy1(
[c1] [datetime] NULL
)
GO
-- successful insert even if the us_english language has a standard setting of mdy
SET DATEFORMAT dmy
SET LANGUAGE 'english'
INSERT INTO #dummy1 SELECT '25/06/2010'
GO
-- successful failed
SET LANGUAGE 'british english'
INSERT INTO #dummy1 SELECT '26/06/2010'
GO
-- insert failed
SET LANGUAGE 'english'
INSERT INTO #dummy1 SELECT '27/06/2010'
GO
SELECT *
FROM #dummy1
DROP TABLE #dummy1
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply