RE: Execute permission lost for nonadmin user after db migration with
From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 10/12/04
- Next message: Mike Epprecht (SQL MVP): "Re: Connection Broken when executing select statement"
- Previous message: Mingqing Cheng [MSFT]: "RE: SQL Server attacking UDP 1434"
- In reply to: dimitrie: "Execute permission lost for nonadmin user after db migration with attach"
- Next in thread: Wayne Snyder: "Re: Execute permission lost for nonadmin user after db migration with attach"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 11 Oct 2004 22:57:04 -0700
Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
>
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
>
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
>
> Thanks,
> Dimitrie
>
>
>
- Next message: Mike Epprecht (SQL MVP): "Re: Connection Broken when executing select statement"
- Previous message: Mingqing Cheng [MSFT]: "RE: SQL Server attacking UDP 1434"
- In reply to: dimitrie: "Execute permission lost for nonadmin user after db migration with attach"
- Next in thread: Wayne Snyder: "Re: Execute permission lost for nonadmin user after db migration with attach"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
