March 7, 2008 at 10:13 am
Hi All,
I wanted to pose a question about configuring security between development and production environments.
In the environment I'm in, developers have dbo on our development servers, but we have scaled that back to a max of read-only for most cases in production. The question I have is: Is there a way to script out the database users and permissions on a development database prior to restoring a production copy of the database?
Basically, developers will request a copy of the production database so that they can develop on a current copy of the DB, and by simply bringing a backup down and restoring it, mapping the logins and users, the individual access that gets pulled down is configured. Therefore, I need to manually make sure they are set up as DBO.
In SQL 2000 EM, you had the option of scripting the database users and it would generate the script so I could do the following:
1. script the database users on development
2. restore a production backup copy
3. map the users to match their logins
4. run the script for database user access
Note that the logins were previously scripted and this is only necessary where the databases already existed in development.
If anyone has any other suggestions to handle this, it would be appreciated.
Previously, the access was carried over into production, so employees who were dbo in development wound up being dbo in production, and going forward we are revoking the access to production to a maximum of read-only or granting execute to specific SPs and such.
Thank you in advance for any help/suggestions.
steve
March 7, 2008 at 1:16 pm
Try checking out the scripts here...
http://www.sqlservercentral.com/Forums/Topic459785-146-1.aspx
¤ §unshine ¤
March 7, 2008 at 6:44 pm
Thanks!
That is close to what I'm looking for on page two of that discussion, but that is also scripted for SQL 2000, and it takes the parameter of a database user name. Certainly that could be modified to loop through all DB users, but I'm interested in something for SQL 2005 that will do something like:
-- db_user_1
EXEC sp_addrolemember 'db_owner', 'db_user_1'
--db_user_2
GRANT EXECUTE ON [dbo].[usp_stored_procedure_1] TO [db_user_2]
Because the SQL 2005 system views differ from the SQL 2000 tables, I'm trying to develop something that isn't referencing the 2000 schema.
I'm working on developing something based on samples I've found around the web and if I get something that works I'll post it.
March 9, 2008 at 11:20 pm
Check if this helps,
http://sql-articles.com/index.php?page=Scripts/scrip_user_05.php
[font="Verdana"]- Deepak[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply