Execute permission lost for nonadmin user after db migration with attach

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,
Dimitriedimitrie 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
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||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
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> 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
>