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 Monday, December 17, 2007 9:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 24, 2014 9:08 AM
Points: 5, Visits: 35
Comments posted to this topic are about the item Login creation in SQL Server 2005 for SQL Server Authentication Mode
Post #434135
Posted Tuesday, December 18, 2007 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 26, 2009 8:33 AM
Points: 15, Visits: 16
Good Article, you should go to the next one, I mean allowing windows users by accessing with autentication mode.
bye ;)
Post #434218
Posted Tuesday, December 18, 2007 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:51 AM
Points: 5, Visits: 27
how to create the abstract views in the sql server 2005 or 2003
is it possible to the database queries
Post #434223
Posted Tuesday, December 18, 2007 9:07 AM
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
I have a question about windows authentication.

when one domain user belong to multiple windows domain group, say grp1 and grp2, and both groups are granted access to database and given different permissions. when this user logon to sqlserver, which path he got? through grp1 or grp2? how does sqlserver decide on taking which SID to authenticate the user?

thanks,

jiulu



Post #434370
Posted Tuesday, December 18, 2007 9:09 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
The article was well done, but it could use some expansion. For instance, it might be worth discussing why you would want to allow SQL Server Authentication for one thing.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #434373
Posted Tuesday, December 18, 2007 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:03 AM
Points: 28, Visits: 125
Is this article from "SQL for Dummies" book? If not, we should open a sub-forum for this kind stuff.



Post #434483
Posted Tuesday, December 18, 2007 1:53 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
zloy (12/18/2007)
Is this article from "SQL for Dummies" book? If not, we should open a sub-forum for this kind stuff.



* Noel
Post #434530
Posted Tuesday, December 18, 2007 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 8, 2011 6:03 PM
Points: 3, Visits: 10
I've noticed that when I Restore a database (in my case to a different machine) that the security settings that were in place on the original database are not replicated on the restored database. In particular, the SQL Server logins do not seem to work. Does anyone know what is happening here and if there is a work around to maintain the original security settings.
Post #434549
Posted Tuesday, December 18, 2007 2:46 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)
I've noticed that when I Restore a database (in my case to a different machine) that the security settings that were in place on the original database are not replicated on the restored database. In particular, the SQL Server logins do not seem to work. Does anyone know what is happening here and if there is a work around to maintain the original security settings.


doug,

the sid in master..syslogin has to be re-created on your restored server if it's not there, the db.dbo.sysusers will have the sid because it's part of the backup.

if you are using sql authentication, then you may have to update dbname.dbo.sysusers sid to match master.dbo.syslogins, because sid will be different from one server to another, but using windows authentication sid will come from windows domain or group, it will not change, it's just a matter of creating the windows group or user on the restored users.

have a look on the result of

select * from master..syslogins
and
select * from yourdbname..sysusers



Post #434552
Posted Tuesday, December 18, 2007 3:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 8, 2011 6:03 PM
Points: 3, Visits: 10
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".
Post #434560
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse