Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Connection Problems Expand / Collapse
Author
Message
Posted Monday, February 7, 2011 2:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:45 AM
Points: 1,853, Visits: 3,449
What security checking does sp_change_users_login do?
All it does is map a database user with a login. BOL says nothing about any security checking.
Post #1059407
Posted Monday, February 7, 2011 2:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 1,733, Visits: 6,324
sp_helptext[sp_change_users_login] will reveal all
Post #1059409
Posted Monday, February 7, 2011 2:36 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
Nice question, but very easy indeed for those of us who have been moving databases from server to server for more than a decade.

Tom
Post #1059413
Posted Monday, February 7, 2011 3:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, August 17, 2014 2:48 PM
Points: 3,352, Visits: 1,481
Toreador (2/7/2011)
sp_helptext[sp_change_users_login] will reveal all


Presumably the permissions on ALTER USER will perform the required security checks on whether the user has permission to perform the action. The sp_change_users_login does seem to do quite a lot, but apart from listing the orphaned users in a database it should be possible to do everything with CREATE LOGIN and ALTER USER.
Post #1059417
Posted Monday, February 7, 2011 3:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, August 17, 2014 2:48 PM
Points: 3,352, Visits: 1,481
Nils Gustav Stråbø (2/7/2011)
What security checking does sp_change_users_login do?
All it does is map a database user with a login. BOL says nothing about any security checking.


It does some checks on the user that is running the procedure. If it's not a member of the db_owner group it won't let you report or update one, and if the user isn't a member of sysadmin it won't let you auto fix.
Post #1059421
Posted Monday, February 7, 2011 3:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
I thought "yeah I know this one, I've had this problem, don't even need to look it up"... but I guess with 71% getting it right it's quite a common issue.

Great question, nice start to the week. Cheers


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1059425
Posted Monday, February 7, 2011 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 5,965, Visits: 8,218
Toreador (2/7/2011)
I wonder why they are withrawing this procedure?

If I have to make a guess, it is probably because over the past years, there has been a consistent move away from stored procedures toward "real" SQL syntax. In SQL Server 2000 (IIRC), CREATE USER, ALTER USER, and DROP USER did not exist at all; there were stored procs for that as well. One by one, the old stored procs are being replaced.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1059472
Posted Monday, February 7, 2011 6:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 7,172, Visits: 6,322
Toreador (2/7/2011)
I always use sp_change_users_login in these circumstances...


Ditto. I did get the correct answer, but that's not been my first choice of resolution in these instances.

Toreador (2/7/2011)

I wonder why they are withrawing this procedure? Anyone know if the replacement command does all the security checking that the procedure does?


And ditto again. I didn't realize (until I checked BOL) that this was deprecated. I haven't read page 2 of this thread yet, so if someone's already answered these questions, thank you. If the questions haven't been answered, could someone enlighten us?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1059494
Posted Monday, February 7, 2011 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 7,172, Visits: 6,322
Hugo Kornelis (2/7/2011)
Toreador (2/7/2011)
I wonder why they are withrawing this procedure?

If I have to make a guess, it is probably because over the past years, there has been a consistent move away from stored procedures toward "real" SQL syntax.


Real SQL? Why in heavens name would any of us want to use "real" SQL? (And does it taste better than the genuine fake SQL?)


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1059498
Posted Monday, February 7, 2011 6:45 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Great question, thanks.

Minor clarification: the 2 hyperlinks listed at the bottom of the explanation didn't contain a notification about future deprecation of the SP (not that I could find, anyway).

That note is present on the page for sp_change_users_login at this location.

Rich
Post #1059506
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse