February 6, 2011 at 7:24 am
Comments posted to this topic are about the item Connection Problems
February 6, 2011 at 11:03 am
Very good scenario based question.
M&M
February 6, 2011 at 2:17 pm
Good Question.
Thanks.
February 6, 2011 at 11:01 pm
Good question.
February 6, 2011 at 11:58 pm
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2011 at 12:53 am
Thank you very much for this question and scenario--and for the update regarding the sp_change_users_login stored procedure.
Regards,
Michael
February 7, 2011 at 1:16 am
This is an old problem with sql2000 too: restoring a database on a different instance, logins to database become invalid.
The solution is create login on the restoring instance with the same sid of the source instance
See here: http://msdn.microsoft.com/en-us/library/ms189751.aspx
CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }
<option_list1> ::=
PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]
<option_list2> ::=
SID = sid | DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
| CREDENTIAL = credential_name
<sources> ::=
WINDOWS [ WITH <windows_options>[ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name
<windows_options> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
February 7, 2011 at 1:20 am
Exactly a scenario that happens in our environments. Thanks for the question!
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
February 7, 2011 at 1:36 am
Thanks for the kind comments everyone - this was my first question, so it's good to get a positive response!
And thanks Carlo for the extra information - I wasn't previously aware you could do that.
February 7, 2011 at 2:15 am
I always use sp_change_users_login in these circumstances, so had to pick an answer at random, luckily I got the right one :).
I wonder why they are withrawing this procedure? Anyone know if the replacement command does all the security checking that the procedure does?
February 7, 2011 at 2:24 am
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.
February 7, 2011 at 2:27 am
sp_helptext[sp_change_users_login] will reveal all
February 7, 2011 at 2:36 am
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
February 7, 2011 at 3:01 am
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.
February 7, 2011 at 3:05 am
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.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy