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 ««12

Login creation in SQL Server 2005 for SQL Server Authentication Mode Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2007 3:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, 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



Post #434565
Posted Wednesday, December 19, 2007 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 28, 2008 2:48 PM
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
Post #434865
Posted Wednesday, December 19, 2007 2:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 08, 2011 6:03 PM
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
Post #434993
Posted Sunday, December 23, 2007 3:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 2:57 AM
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,
Post #436057
Posted Tuesday, December 25, 2007 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 25, 2007 10:48 PM
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 "
Post #436296
Posted Tuesday, June 03, 2008 5:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 03, 2008 4:59 AM
Points: 1, Visits: 0
I need to give readonly access to a windows authenticated user. can any one suggest
Post #510550
Posted Monday, May 31, 2010 2:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 31, 2010 2:10 AM
Points: 1, Visits: 0
thnx nice article
Post #930273
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse