Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Proper use of "If Exists" in a script
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

651 Posts

Posted - 02/19/2013 :  10:25:04  Show Profile  Reply with Quote
I've scripted out the Roles from a specific db to import into the MSDB under System databases (why is not relevant, it's what i was told to do). The script will be run against a new server where other databases are being restored to. The script is obviously a bunch of "Create Role" lines for the roles themselves, however, i need each line to check if the role already exists. For instance i have

Use [msdb]
Create Role [DatabaseMailUserRole] AUTHORIZATION [dbo]

I assume using an IF EXISTS would look something like this:

use msdb
IF EXISTS (SELECT * FROM (what goes here?) WHERE ROLE=[DatabaseUserRole]) ELSE
Create Role [DatabaseMailUserRole] AUTHORIZATION [dbo]

I should also add that i need to script out the roles from the other databases which are being backed up from one server and restored to another as it appears that the roles and some other objects are not being restored when these backup/restores are being done.

Just wondering if i'm approaching this correctly.



James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/19/2013 :  11:43:38  Show Profile  Reply with Quote
   SELECT * FROM sys.database_principals
   WHERE [type] = 'R' AND [name] = 'DatabaseMailUserRole'

Go to Top of Page

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/19/2013 :  11:46:14  Show Profile  Reply with Quote
To answer you first question:
	name = '<RoleName>'
	AND type = 'R'

As far as backup and restores go, the Role should be there after a restore. However, you'll probalby need to associate the Role Members depending on how you have that set up.
Go to Top of Page

Aged Yak Warrior

651 Posts

Posted - 02/19/2013 :  12:47:10  Show Profile  Reply with Quote
Thanks guys.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000