SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connection Problems


Connection Problems

Author
Message
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2477 Visits: 3575
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.
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2819 Visits: 8084
sp_helptext[sp_change_users_login] will reveal all :-)
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14670 Visits: 12238
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

Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3606 Visits: 1552
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.
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3606 Visits: 1552
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.
DugyC
DugyC
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 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 :-D

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11274 Visits: 12005
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
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15172 Visits: 9006
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15172 Visits: 9006
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?) w00t

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1565 Visits: 3665
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search