Michael Maddison
michael at ddisolutions.com.au
Thu Nov 17 22:32:12 CST 2011
Hi JC, I think this is the one you need. Basically when you copy a db from 1 server to another the PID? Could be different on the new server. The sproc below fixes it. C. Automatically map a user to a login, creating a new login if necessary This example shows how to use the Auto_Fix option to map an existing user to a login with the same name, or create the SQL Server login Mary with the password B3r12-36 if the login Mary does not exist. USE pubs go EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36' Go Cheers Michael -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, 18 November 2011 4:29 AM To: Sqlserver-Dba Subject: [dba-SQLServer] Setting up security in new server My client has brought up a new server which will host his Access database back ends as well as SQL Server. The old server ran Windows 2000 X32 and 4 gigs of memory. Windows 2000 only supports SQL Server 2005 due to the fact that it cannot support the .Net framework version required by SQL Server 2008. As a result I was running SQL Server 2005 Express X32 on the old server. For the moment the new server is running Windows 2003 X64 which does support the .Net framework version to support SQL Server 2008. I have installed the .net framework and SQL Server 2008 Express on the new server. I then simply disconnected the database and copied the data file. I need to move a couple of databases from the old server to the new. In the old server I had created two SQL Server users, DiscoAdmin and DiscoUser. In the new server I can set up those users in the server security / logins tab but from there I can't assign the user rights in the databases I brought over from the old server. My guess is that when I do that in the server and assign the rights for the databases, the users already exist in the database itself and so cannot be created. I am unable to delete the user in the database because it says the user "owns a schema". Sure enough the user has a couple of check boxes checked but they are grayed out and I cannot uncheck them. So how the heck to I recreate the user / password / rights stuff that was set up on the old server for these databases? Any help much appreciated. -- John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com ----- No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1411 / Virus Database: 2092/4021 - Release Date: 11/16/11