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


Login creation in SQL Server 2005 for SQL Server Authentication Mode


Login creation in SQL Server 2005 for SQL Server Authentication Mode

Author
Message
sunjiulu
sunjiulu
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 35
Doug Krawec (12/18/2007)
sunjiulu,

Thanks for the explanation.

Yes, I am using SQL Server login rather than Windows Authentication. We have created a SQL Login of the form "ApplicationUser" with access to the single application database. The database I'm restoring is a copy of Production which is being used on a development box.

Is there a standard script that can be used to reset this SID on the restored database to match that of the instance of the SQL Server your are restoring to? I would assume you would need to check for the existence of "ApplicationUser".


doug,

the following sql is what I do in sql2000, I believe sql2005 has a system procedure to do it, maybe a google search will give you the name of the proc. here you go.

/*restore the database*/
restore database yourdbname from disk='d:\dropit\backup.BAK'
...
/*allow update system tables*/
sp_configure 'allow update', 1
reconfigure with override
/*get the sid*/
select sid,name from syslogins where name='yourloginname'
/*update system table to match the sid*/
update yourdbname..sysusers set sid=0x91729E827E48874A8883B8E08A9426F8 where name='yourloginname'
/*not allow update system tables*/
sp_configure 'allow update', 0
reconfigure with override



John Bedi
John Bedi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
When SQL authenication is necessary and there are a number of Logins required, I have attempted to use SQL to created the logins. The 3 checkboxes in the form need to be unchecked. Without this the logins do not function and you can not uncheck the boxes afterwards and have the login work. You must delete and recreate the login using the GUI. Is there something I am missing that would allow the creation of multiple users via SQL?

John
Doug Krawec
Doug Krawec
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
I did a search and found that SQL 2000 does have a system procedure called sp_change_users_login
which does fix the database login to match the SQL Server instance.

MSDN link is:
http://msdn2.microsoft.com/en-us/library/ms174378.aspx


Format I ended up using is: (Note, I've left off the optional SQL User (if you are mapping to a different user) and password at end):

USE your_database
GO
EXEC sp_change_users_login 'Auto_Fix', ' '
GO
Frederik Carlier
Frederik Carlier
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 23
hello,

looking at your previous post, I presume that you have created a production and a testdatabase and that you do a restore to a testdatabase but that it doesn't allow the authenticated user to logon to the testdatabase, as sunjiulu suggest this is because the ssid's of the sql-accounts do not match,

in another post, it is discribed how to copy users from one server to another, see this link on another sql-forum for a way to copy the users so that your app will also work on the other database.

link : http://blogs.techrepublic.com.com/howdoi/?p=140&tag=nl.e138

with kind regards,
awadahmed_120076
awadahmed_120076
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
thank you my dear but i face problem when i login by created user name
error appears "this user not trusted "
vamsipvk
vamsipvk
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
I need to give readonly access to a windows authenticated user. can any one suggest
nitish 53286
nitish 53286
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
thnx nice article
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