February 13, 2008 at 2:10 pm
Hi.. I am using SQL Server 2005.
I am Generating SQL Scripts to re-create all objects in a database.. to create a new database.
I know how to do this.. right-click on the database, Generate Scripts, script all objects, and change a few settings (Create Database, Object-Level Permissions, Triggers, etc.).
The one thing that does NOT get created are the non-admin Database level Roles. db_datareader, db_datawriter.
I need for these roles to get copied.. how can I generate my script to grant these on the new database?
Thanks.
Jason
February 13, 2008 at 2:47 pm
Fixed database roles are always created when a database is created so you shouldn't have to script them. If you mean you want to script the members of the roles, you can probably adapt this to work:
select 'sp_addrolemember ' + '''' + g.name + '''' + ',' + '''' + m.name + ''''
from sys.sysmembers join sys.sysusers g on groupuid = g.uid
join sys.sysusers m on memberuid = m.uid
Greg
February 13, 2008 at 10:50 pm
Or u can use the Generate scripts to script out all the users and logins.
"Keep Trying"
February 14, 2008 at 7:11 am
Chirag (2/13/2008)
Or u can use the Generate scripts to script out all the users and logins.
What's what I did.. but the database role memberships did not get scripted.
What I want is to right-click the database, select "Generate Scripts" and ALL of the permissions are included - not just Object Level.
Or is there some other setting that I haven't found?
February 14, 2008 at 7:15 am
Greg Charles (2/13/2008)
Fixed database roles are always created when a database is created so you shouldn't have to script them.
I don't get what you mean.. fixed database roles are always created - ?
What I want is to right-click an existing database, Generate Scripts, rename the database in the .sql script, execute the .sql script, and an exact clone of the database appears on my server - and user "Charles" has the same permissions on this new database (INCLUDING db_datareader and db_datawriter) as "Charles" had on the old database.
I am looking for this WITHOUT typing or running extra scripts at the end.
February 14, 2008 at 9:06 am
I wasn't sure, from reading your original post, if you wanted to script the creation of the roles themselves. Your subsequent posts make it clear that you want to script role membership assignments.
I haven't found a way to make the SSMS "Generate Script" produce role assignment statements. I wouldn't mess with scripts anyway if you want to duplicate the database. Just restore a backup of the database. You'll get everything including permissions and roles.
Greg
February 14, 2008 at 12:17 pm
Greg Charles (2/14/2008)
I wouldn't mess with scripts anyway if you want to duplicate the database.
Actually, that is exactly it.
I want to duplicate the database.. EXCEPT for the data.
(Also there will be a new db name)
I know how to write a T-SQL to back up the database to a drive, then restore it under another name. But the database I'm working with is 30GB in size.. I want the same STRUCTURE, with none of the data.
(Of course I could back up the db, restore the db, then truncate every table.. but that would take way too long when we are dealing with 30GB+ databases)
February 15, 2008 at 12:12 am
If you are moving to a different server or instance, you will need to transfer the LOGINs before creating the Database, see:
http://support.microsoft.com/kb/918992
Andy
February 17, 2008 at 2:43 am
I have the same problem. Using SSMS it's not possible is it necessary to do this manually?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
February 17, 2008 at 10:12 am
Jason Wisdom (2/14/2008)
Greg Charles (2/14/2008)
I wouldn't mess with scripts anyway if you want to duplicate the database.Actually, that is exactly it.
I want to duplicate the database.. EXCEPT for the data.
(Also there will be a new db name)
I know how to write a T-SQL to back up the database to a drive, then restore it under another name. But the database I'm working with is 30GB in size.. I want the same STRUCTURE, with none of the data.
(Of course I could back up the db, restore the db, then truncate every table.. but that would take way too long when we are dealing with 30GB+ databases)
The easiest way to do this is to use a comparison tool (SQL Compare from Red-Gate is my preference).
Create a new blank database, use SQL Compare to compare between your existing database and the new blank database. SQL Compare will generate all of the scripts you need to create all of the objects in the new blank database.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 20, 2008 at 2:52 pm
I want to duplicate the database.. EXCEPT for the data.
The easiest way to do this is to use a comparison tool (SQL Compare from Red-Gate is my preference).
Create a new blank database, use SQL Compare to compare between your existing database and the new blank database. SQL Compare will generate all of the scripts you need to create all of the objects in the new blank database.
Jeff[/quote]
This will generate all scripts... fixed database roles, etc.?
Now can this be automated?
What I need is a scheduled routine (a Job or Maintenance Plan) that will go in, nightly, and duplicate Database A's structure, and use it to create Database B.
February 20, 2008 at 3:50 pm
Yes - it will generate all scripts. I do not know whether or not it can be automated - but it is easy enough to find out. Download the trial version and test it out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 5, 2008 at 8:11 am
Thought I would chime in on this topic. We noticed the same thing regarding the inability to generate a script containing the members of fixed database roles in 2005. This is one of several features that existed in 2000 and was removed in 2005. We are guessing 2008 will have these features. It just seams that Microsoft was rushing to make a release date and had to cut some corners with 2005. I'm calling them this week regarding how it takes us nearly one hour to script all objects (3000+) in a single database using the Management Studio. It took a fraction of this time in 2000. Granted security is more complex in 2005, but one hour to script a database is not acceptable.
Just my 2 cents.
Dave
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply