Server Logins

Question

Post reply

Server Logins

Steve Jones - SSC Editor

SSC Guru

Points: 736270
More actions
May 3, 2015 at 5:23 pm

#297766

Comments posted to this topic are about the item Server Logins

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Mr. Kapsicum

SSCertifiable

Points: 6128

May 4, 2015 at 12:12 am

#1795292

Very good question. Thanks

Xavon SSCrazy Points: 2280 More actions · Answer 2

Interesting question.

But I am disappointed the QotD was not May the 4th related.

g.britton SSChampion Points: 13863 More actions · Answer 3

I'm still looking for mention of "get or set the authentication message" in the reference link. I can't find it there, though the property "specifies the type of authentication" (not message!)

Am I being too literal?

Gerald Britton, Pluralsight courses

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

g.britton (5/4/2015)
I'm still looking for mention of "get or set the authentication message" in the reference link. I can't find it there, though the property "specifies the type of authentication" (not message!)
Am I being too literal?

I didn't find it either. A DBA being literal? Who ever heard of such a thing? 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Xavon (5/4/2015)
Interesting question.
But I am disappointed the QotD was not May the 4th related.

sorry, too busy and didn't think of it. Anyone want to send me one for next year 😉

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Ed Wagner (5/4/2015)
g.britton (5/4/2015)
I'm still looking for mention of "get or set the authentication message" in the reference link. I can't find it there, though the property "specifies the type of authentication" (not message!)
Am I being too literal?
I didn't find it either. A DBA being literal? Who ever heard of such a thing? 😉

I don't think LoginType gets or sets any sort of authentication message. It gets or sets the account type for the login.

However, that didn't make the question difficult, since the nonexistent "authentication type" couldn't be the answer, State is readonly so the interface can't set it, and HasAccess although it can be set as well as got merely determines whether the login has access to the instance, which leaves only Login.Type as a vaguely viable candidate (only because the authentication message is probably dependent on what sort of account th login is for which surely has some kind of influence on the authentication message :-D.

Tom

Bobby Russell SSCrazy Points: 2801 More actions · Answer 7

The "correct answer" is incorrect. That sets and gets the login METHOD not the MESSAGE. HASACCESS communicates the MESSAGE. +1

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

I don't understand why anyone would need to do this through PowerShell. Can someone who actually had such a need please explain? I'm looking for someone that actually did it this way for a good reason.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Jeff Moden (5/4/2015)
I don't understand why anyone would need to do this through PowerShell. Can someone who actually had such a need please explain? I'm looking for someone that actually did it this way for a good reason.

I use PowerShell+SMO to sync Logins between servers, e.g. servers hosting related AlwaysOn replicas and servers related through Log Shipping. It allows me to run the PowerShell script from a central utility server (one that does not necessarily have to have an installed instance of SQL Server) and touch all related servers in the environment. When new servers are added to the environment I add only have to edit the configuration data the PowerShell script refers to in order to get Login syncing working across the new servers.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

Jeff Moden (5/4/2015)
I don't understand why anyone would need to do this through PowerShell. Can someone who actually had such a need please explain? I'm looking for someone that actually did it this way for a good reason.

PowerShell, definitely not me 😉

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Orlando Colamatteo (5/5/2015)
Jeff Moden (5/4/2015)
I don't understand why anyone would need to do this through PowerShell. Can someone who actually had such a need please explain? I'm looking for someone that actually did it this way for a good reason.
I use PowerShell+SMO to sync Logins between servers, e.g. servers hosting related AlwaysOn replicas and servers related through Log Shipping. It allows me to run the PowerShell script from a central utility server (one that does not necessarily have to have an installed instance of SQL Server) and touch all related servers in the environment. When new servers are added to the environment I add only have to edit the configuration data the PowerShell script refers to in order to get Login syncing working across the new servers.

That's cool. Thanks Orlando.

I thought that CMS was supposed to take care of stuff like that now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 12

Thanks for the question. Maybe there should be an AuthenticationType.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

Jeff Moden (5/5/2015)
Orlando Colamatteo (5/5/2015)
Jeff Moden (5/4/2015)
I don't understand why anyone would need to do this through PowerShell. Can someone who actually had such a need please explain? I'm looking for someone that actually did it this way for a good reason.
I use PowerShell+SMO to sync Logins between servers, e.g. servers hosting related AlwaysOn replicas and servers related through Log Shipping. It allows me to run the PowerShell script from a central utility server (one that does not necessarily have to have an installed instance of SQL Server) and touch all related servers in the environment. When new servers are added to the environment I add only have to edit the configuration data the PowerShell script refers to in order to get Login syncing working across the new servers.
That's cool. Thanks Orlando.
I thought that CMS was supposed to take care of stuff like that now.

I use CMS for lots of stuff but am not using it for this, although I suppose I could leverage it. CMS offers a piece of what I need to know in the PowerShell script, namely the server list, however the list itself does not tell me which servers should be synced to which other servers. I could devise and adhere to some organization scheme in CMS that would help me know the source and target but that could go horribly wrong if someone made a mistake when editing CMS entries.

Another option would be for me to add some clever code to the PowerShell script that figured out from the System Views & System Tables on the source instance which target instances should have their Logins synced but I haven't taken my PowerShell script to that level.

You have given me some food for thought just by raising the idea of using a CMS, so thanks. For now at least, the maintenance overhead of adding an entry to the configuration my PowerShell is driven by when new servers are added in my environment is low enough.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 14

Jeff Moden (5/4/2015)
I don't understand why anyone would need to do this through PowerShell. Can someone who actually had such a need please explain? I'm looking for someone that actually did it this way for a good reason.

You don't need to, but certainly if you did any string manipulation or wanted to make this easy for others, including some validation or other programming, PoSh might be easier than SQLCMD with a parameter.

PoSh isn't necessarily better at tasks. However it does make it easier at scale, and it can be quicker. Certainly quicker for repetitive stuff run outside of SQL Server, across systems.