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 |
|
uberbloke
Yak Posting Veteran
67 Posts |
 Posted - 2002-01-23 : 04:31:28
|
| I have a development server with two users (lets call them "company" and "dispatch")These users have NO permission on tables and individual permissions on SPs (so company has exec on sp1, 2, 3, 5 and dispatch has exec permissions on sp1, 3, 4)How do I move this to the production server retaining the logins/passwords and permissions.I have run a search and discovered [url]http://www.swynk.com/friends/boyle/fixingbrokenlogins.asp [/url] and the sp_change_users_login sp but I am obviously having a thick moment... this restores the logins yes? but how do I retain the permission on the sps? Am I missing something basic here (If I do a restore and sp_change_users_login will all the permissions magically sort themselves out?)Please, a dummies guide would be appreciated! |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
|
|
uberbloke
Yak Posting Veteran
67 Posts |
Posted - 2002-01-23 : 06:32:12
|
| Unfortuanately I can't use DTS; has to be via a DB restore.I am thinking that a script to create users/logins and assign SP permissions may be the way to go... but am not sure where to start (apart from BoL of course!)Any advice gratefully received (as ever :-) |
 |
|
|
uberbloke
Yak Posting Veteran
67 Posts |
Posted - 2002-01-23 : 06:38:51
|
| Hmmm, found the script object level permission in EM generate sql script for stored procs...Looks like I should be able to use this as starting place and just mangle the produced output to suit my needs. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-23 : 08:49:27
|
quote:
Am I missing something basic here (If I do a restore and sp_change_users_login will all the permissions magically sort themselves out?)
Well, there will be no magic involved, but yes, permissions will be properly set. You can re-read Neil Boyle's article -- it actually explains things pretty well. Basically, it comes down to the fact that permissions are set within the database (therefore when a database is moved -- permissions are moved with it), and logins exist at a server level (therefore when you move a database to a new server, you typically lose mapping of database users to server logins). Because of this, once you fix the mapping by running sp_change_users_login, all permissions you had set in the past will be in place.-------------------If trainstation is a place where train stops, what's a workstation?Edited by - izaltsman on 01/23/2002 08:55:05 |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-23 : 12:00:32
|
| Izaltsman is right. Just restore your DB, recreate the logins, and run sp_change_users_login 'update_one', user, login (For each user/login pair) it will automagically fix everything up for you.-Chad |
|
|
|
|
|
|
|
|
Restore BKP w/logins and SP permissions