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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Proper use of "If Exists" in a script

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-02-19 : 10:25:04
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
Master Smack Fu Yak Hacker

3873 Posts

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-19 : 11:46:14
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

651 Posts

Posted - 2013-02-19 : 12:47:10
Thanks guys.
Go to Top of Page
   

- Advertisement -