如何找到该用户所属的project server组

　　今天一同事问我如何判断登陆用户属不属于某Project Server组，我一开始就想到sharepoint里面的SpUser组，但详聊才知道Project Server工作组和sharepoint组是不一样的，它有其特殊的权限，这里就不再介绍其权限。

　　根据我的经验，ProjectServer读取数据一般都比较简单，如果是增删改就必要调用PSI，相对来说比较复杂，于是我直接找到数据库，找到Reporting数据库，没找到和Group比较相近的表，找到Published库，找到了表[ProjectServer_Published].[dbo].[MSP_WEB_SECURITY_GROUP_MEMBERS]和表[ProjectServer_Published].[[dbo].[MSP_WEB_SECURITY_GROUPS]，很快就找到了我们所说的ProjectServer工作组。

　　管理员组
　　主管人员
　　项目组合经理组
　　项目经理组
　　资源经理组
　　工作组领导组
　　工作组成员组

     我们将两个表连接起来，就很轻松的找到ProjectServer组关联的对象，但一问题随之而来，我们[ProjectServer_Published].[dbo].[MSP_WEB_SECURITY_GROUP_MEMBERS]的字段WRES_GUID字段应该关联用户ID，可我们找到MSP_RESOURCES却没关联上，这是个问题？

没办法，我想到了暴力搜索，我得到了WRES_GUID，我就暴力搜索Published库的所有表所有字段，看哪个字段的值和[ProjectServer_Published].[dbo].[MSP_WEB_SECURITY_GROUP_MEMBERS]的字段WRES_GUID一样。

关于如何暴力搜索，请查看

http://blog.csdn.net/oulei2008/article/details/7435692

代码如下：

protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = Query("SELECT Name FROM ProjectServer_Published..SysObjects Where XType='U' ORDER BY Name").Tables[0];

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            DataTable dt2 = Query2("select a.name as [column],b.name as type from syscolumns a,systypes b where a.id=object_id('" + dt.Rows[i][0].ToString() + "') and a.xtype=b.xtype").Tables[0];
            //DataTable dt2 = Query2("SELECT Name FROM SysColumns WHERE id=Object_Id('"+ dt.Rows[i][0].ToString()+ "')").Tables[0];
            for (int j = 0; j < dt2.Rows.Count; j++)
            {
                if (dt2.Rows[j][1].ToString() == "uniqueidentifier")
                {
                    DataTable dt3 = Query2("select * from " + dt.Rows[i][0].ToString() + " where " + dt2.Rows[j][0].ToString() + " = '293EC7E6-18A3-4228-B7A7-33352594F15B' ").Tables[0];
                    if (dt3.Rows.Count > 0)
                    {
                        Response.Write(dt.Rows[i][0].ToString() + "    " + dt2.Rows[j][0].ToString()+" </br>");
                    }
                }
            }
        }
    }


    public DataSet Query(string SQLString)
    {
        //using (SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=ProjectServer_Reporting;User ID=sa;password=sa"))ProjectServer_Published

        using (SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=master;User ID=sa;password=123@abc"))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                //command.Fill(ds,"ds");
                command.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return ds;
        }
    }

    public DataSet Query2(string SQLString)
    {
        //using (SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=ProjectServer_Reporting;User ID=sa;password=sa"))ProjectServer_Published

        using (SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=ProjectServer_Published;User ID=sa;password=123@abc"))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                //command.Fill(ds,"ds");
                command.Fill(ds);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return ds;
        }
    }

很快，搜索出数据

MSP_RESOURCES RES_SECURITY_GUID
MSP_WEB_SECURITY_GROUP_MEMBERS WRES_GUID
MSP_WEB_SECURITY_GROUP_MEMBERS WSEC_GRP_GUID

原来是在表MSP_RESOURCES中，只不过它没有直接拿MSP_RESOURCES的主键也就是用户的GUID来标识，而是在表里另外增加了字段。

查询字段为：

select c.RES_NAME,a.WSEC_GRP_NAME from [dbo].[MSP_WEB_SECURITY_GROUPS] as a join
[dbo].[MSP_WEB_SECURITY_GROUP_MEMBERS] as b
on a.WSEC_GRP_GUID = b.WSEC_GRP_GUID
left join dbo.MSP_RESOURCES as c on b.WRES_GUID = c.RES_SECURITY_GUID