SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

WJHamel
Aged Yak Warrior

USA
614 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]
GO


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]
GO


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.

thanks

james

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/19/2013 :  11:43:38  Show Profile  Reply with Quote 
IF EXISTS
(
   SELECT * FROM sys.database_principals
   WHERE [type] = 'R' AND [name] = 'DatabaseMailUserRole'
)
....
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3829 Posts
Posted - 02/19/2013 :  11:46:14  Show Profile  Reply with Quote
To answer you first question:
SELECT 
	*
FROM 
	sys.database_principals
WHERE 
	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

WJHamel
Aged Yak Warrior

USA
614 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  
 New 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