Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-05-21 : 11:43:53
|
| All,I have a situation where i am unable to move ahead. I have a SQL 2008 database and we are planning to move it to SQL 2008 R2. Migrating is simple enough....1.)Backup the database2.)i have 5 sql logins in this database..so i guess i need to pull those logins and run them on destination server...3.)sp_detach4.)sp_attach5.)Run the output from step 2.6.)check for orphan users and fix them.i guess...the sql logins pull info through webservers.But here is the place where i am unable to move ahead...i want to remove SQL Logins and wanted to implement integrated security. So what are the steps which i need to take for implementing integrated security? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-21 : 17:10:15
|
| So if the application supports the integrated security..what would be the process? When you said manually..does it mean that..all the sql logins should be created manually on the webservers active directory? |
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-21 : 18:31:51
|
| Also, when we move the database, how can we move only the specific logins of that database? i think sp_help_revlogin pulls the login information of all the users of a server. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-05-22 : 02:01:02
|
| 1)Assuming you're using AD - the accounts have to be in the AD - then add them as Login accounts on the SQL Server. Ensure the login accounts are mapped appropriately to the database user accountsJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-22 : 10:12:14
|
| Jack -- Thanks for the reply. Currently we are using SQL Logins.We want to convert them to Integrated Security(Windows authentication)..By any chance, do you know what would be the process for converting this? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-22 : 12:38:23
|
| The process is going to be a manual process. You have to identify each login in SQL Server that you are going to convert from a SQL login to a Windows login. Once you have identified this login, you have to identify where that login is connecting to the database.Is that login only used from the web application?Is that login a user that accesses both through the web application and through SSMS?Will that login be used to authenticate to the web application - and to the database system? Note: if this is the case, it gets much more complicated because now you have to make sure you implement Kerberos.Once you know how that login will be connecting, then you can create the new windows login and add a new user to the database with the appropriate permissions. Then, you go to each location where that login will be used and modify the connection to use integrated security.Again, it is going to be a manual process and you should really take a single login at a time and change it. I would recommend that you test this change in your test system first to make sure it can be made without causing problems.Jeff |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-22 : 14:37:38
|
| Thanks Jeff!! your explanation is veryclear, now i can start working on this a little bit. |
|
|
|
|
|
|
|
|
database move along with security implementation