Re: loss of permissions
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Fri, 3 Aug 2007 09:19:55 -0400
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
"bic" <bic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:764C3CBF-BB33-4850-82EC-85081E667071@xxxxxxxxxxxxxxxx
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
.
- Prev by Date: Re: location of DTSX packages, msdb or file system?
- Next by Date: SSIS pulling data from Excel 2007
- Previous by thread: Re: location of DTSX packages, msdb or file system?
- Next by thread: SSIS pulling data from Excel 2007
- Index(es):
Relevant Pages
|
