How to determine mirroring role?

Once I have identitied that mirroring is enabled on database via SMO Database.IsMirroringEnabled, I need to determine the mirroring role. I noticed an enumerated type - MirroringRole, but no SMO method/property to access it.

I have tried to query the sys.database_mirroring table directly, but this fails with an exception on the mirror database - as it is being mirrorred :-(

What is the recommended way to determine the mirroring role?

Thanks, Nick

You might try in calling from a C# embedded in SMO, like converting the value from c# to smo.|||

Not sure what you mean, I have tried querying the database ...

String query = String.Format("SELECT mirroring_role FROM sys.database_mirroring WHERE mirroring_guid = '{0}'", db.MirroringID.ToString());

DataSet dsResultSet = db.ExecuteWithResults(query);

.. but this causes an exception as the database is being mirrored.

+ base {"Execute with results failed for Database 'Mms'. "} Microsoft.SqlServer.Management.Smo.SmoException {Microsoft.SqlServer.Management.Smo.FailedOperationException}
+ InnerException {"The database \"Mms\" cannot be opened. It is acting as a mirror database."} System.Exception {System.Data.SqlClient.SqlException}

Thanks, Nick

|||

You don't need to connect to the mirror database (you can't, as you have experienced). You can connect to the master database on the mirror server and execute the following query:

SELECT m.mirroring_role_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE d.name = '<your_database_name_here>'

How to determine mirroring role?

Once I have identitied that mirroring is enabled on database via SMO Database.IsMirroringEnabled, I need to determine the mirroring role. I noticed an enumerated type - MirroringRole, but no SMO method/property to access it.

I have tried to query the sys.database_mirroring table directly, but this fails with an exception on the mirror database - as it is being mirrorred :-(

What is the recommended way to determine the mirroring role?

Thanks, Nick

You might try in calling from a C# embedded in SMO, like converting the value from c# to smo.|||

Not sure what you mean, I have tried querying the database ...

String query = String.Format("SELECT mirroring_role FROM sys.database_mirroring WHERE mirroring_guid = '{0}'", db.MirroringID.ToString());

DataSet dsResultSet = db.ExecuteWithResults(query);

.. but this causes an exception as the database is being mirrored.

+ base {"Execute with results failed for Database 'Mms'. "} Microsoft.SqlServer.Management.Smo.SmoException {Microsoft.SqlServer.Management.Smo.FailedOperationException}
+ InnerException {"The database \"Mms\" cannot be opened. It is acting as a mirror database."} System.Exception {System.Data.SqlClient.SqlException}

Thanks, Nick

|||

You don't need to connect to the mirror database (you can't, as you have experienced). You can connect to the master database on the mirror server and execute the following query:

SELECT m.mirroring_role_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE d.name = '<your_database_name_here>'

How to Determine if a user is a member of the System Admin role?

Is there a script/function that can be used to determine if a user (granted
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')

> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||Hello Brad,
As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:
============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id
inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================
As you can see, we need to query multiple catalog views in sys schema.
While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub