July 30, 2013 at 12:38 pm
I am unable to use T-SQL statement "CREATE LOGIN xxx\yyy FROM Windows" and create a Login in SQL Server. I can manually go through SSMS and create the login by doing 'New Login' but when I try to do with T-SQL, it says that xxx\yyy is not a windows user or group and errors out. When I do manually through SSMS, it does recognise that account but not with T-SQL.
What am I missing?
July 30, 2013 at 3:13 pm
Have you tried this notation?
CREATE LOGIN [domain\login] FROM WINDOWS;
Maybe a domain name or login are not regular identifiers.
July 30, 2013 at 3:51 pm
In SSMS, use the Script button to see what command that SSMS generates. Keep in mind that SSMS has no secret interface to SQL Server, but it uses T-SQL just like you and me.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 3:25 am
e4d4 (7/30/2013)
Have you tried this notation?
CREATE LOGIN [domain\login] FROM WINDOWS;Maybe a domain name or login are not regular identifiers.
This will work. You must enclose the login name with brackets [] when you create a login from a domain account.
July 31, 2013 at 5:59 am
Erland Sommarskog (7/30/2013)
Keep in mind that SSMS has no secret interface to SQL Server, but it uses T-SQL just like you and me.
Well, mostly. Not that it's secret, but it does also use DMO/SMO for lots of the scripting capability, which aren't accessible via T-SQL.
But yes, in the example of creating logins, this is plain T-SQL.
July 31, 2013 at 6:09 am
HowardW (7/31/2013)
Well, mostly. Not that it's secret, but it does also use DMO/SMO for lots of the scripting capability
It is correct that SSMS uses SMO, but guess how SMO speaks to SQL Server: T-SQL.
The point is that when you have a one-tier tool that provides some kind of command-line interface, the GUI is not likely use to the command-line but some API which may or may not be public. When you use SQL Server, there is no such secret API, but all clients has to use TDS and thus T-SQL. At most, the tool may use undocumented stored procedures, but about the only corner of SSMS where this happens at any volume is Policy-Based Management.
, which aren't accessible via T-SQL.
Horribly enough, I've seen people accessing DMO from T-SQL, through sp_OAmethod. SMO should be possible to access through a CLR stored procedure.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 6:16 am
Erland Sommarskog (7/31/2013)
Horribly enough, I've seen people accessing DMO from T-SQL, through sp_OAmethod. SMO should be possible to access through a CLR stored procedure.
Ha! Actually, from what I've heard, you can't even add the SMO class into a CLR (even under UNSAFE), but obscure workarounds aside, my point was just that it's not all just T-SQL behind the scenes.
July 31, 2013 at 6:23 am
HowardW (7/31/2013)
my point was just that it's not all just T-SQL behind the scenes.
And my point is that that's exactly what it is - even if there is SMO in between.
OK, so there is one true exception: BCP which uses a command that is only available through TDS, not T-SQL.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 6:31 am
Erland Sommarskog (7/31/2013)
HowardW (7/31/2013)
my point was just that it's not all just T-SQL behind the scenes.
And my point is that that's exactly what it is - even if there is SMO in between.
OK, so there is one true exception: BCP which uses a command that is only available through TDS, not T-SQL.
I seem to remember there are some properties of objects that are literally inaccessible through system tables/DMVs and are only accessible through SMO, struggling to find an example at the moment though!
That aside, there's an awful lot of logic built into SMO. Even if you could theoretically build the equivalent logic in T-SQL, the point still stands that it's a different interface without an obvious mapping into T-SQL code.
July 31, 2013 at 6:45 am
HowardW (2013-07-31)
I seem to remember there are some properties of objects that are literally inaccessible through system tables/DMVs and are only accessible through SMO, struggling to find an example at the moment though!
SMO may of course use undocumented commands or DMVs. However, everything SMO can do, you can do directly. There is no way you can set up that some commands can only be accessed from a certain API.
And, yes, SMO includes an awful lot of logic. Not the least awful as I understand.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply