sql server dts: Hi,

How do I prevent permission wiped from my SPs when it comes to restoring
dbs? Is there a way to assign permission globally? Thanks.
--

bic,

This would probably be better posted in microsoft.public.sqlserver.server if
you have future questions in this vein.

When you restore your database, you lose the permissions that had been
granted to your stored procedures. The only way to 'globally' assign
permissions at the server level is to use server level permissions, all of
which would be pretty heavy for you need. (Making everyone system
administrator is not a good idea.)

Your problem is that your database's permissions (users, roles, and grants)
are not the same as the database that you are restoring from. (If I
understand correctly, I would probably call this a database 'refresh'.) A
couple of things will help:
1. Script out roles, users, their role memberships, and any permissions
(GRANT, REVOKE, DENY) before restoring over your current database.
2. After the restore drop the users (if appropriate) from the restored
database.
3. Run your script to reapply all the permissions..

It is a good practice to always GRANT and REVOKE rights to roles, not to
particular users. This contains the scope of grants and makes management a
little easier. (It also makes it easier to give Fred the same rights Judy
has.)

RLF


[quoted text, click to view]

SP?
Service Provider?
Service Pack?

What does database permissions have to do with DTS?

Not sure I understand your question.

--
Todd C

[quoted text, click to view]