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.
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 haveUse [msdb]Create Role [DatabaseMailUserRole] AUTHORIZATION [dbo]GO I assume using an IF EXISTS would look something like this:use msdbIF EXISTS (SELECT * FROM (what goes here?) WHERE ROLE=[DatabaseUserRole]) ELSECreate 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.thanksjames |
|
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] |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-19 : 11:46:14
|
To answer you first question:SELECT *FROM sys.database_principalsWHERE 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. |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2013-02-19 : 12:47:10
|
Thanks guys. |
|
|
|
|
|