CODEWITHSUNDEEP
c#databasesql-server-2008-r2
Tolga Evcimen
Tolga Evcimen

Reputation: 7352

checking database rights on certain user

I am given connection to a database which is owned by another company. The user that they gave to me has restricted privilidges, meaining that I can only make select queries on certain views.

I got a little problem here since the other company is not being so cooperative. They change my users password without telling me, or they change the names of the views. Since there are more than 40 views I want to make an automatic system that checks if everything is alright.

My question is what kind of checks I can make on the views and database? is just trying the connection to open and making select * queries for each view enough?

BTW the database is SQLServer 2008 R2 and I use C#.

Upvotes: 1

Views: 91

Answers (1)

Avi Turner
Avi Turner

Reputation: 10466

Here is a function for checking that all required views exists:

bool IsAllviewsExists()
{ 
    string DatabaseName= "Your_DB_NAME";
    string[] viewsInDB = GetAllViewsNamesInDB();
    for (int i = 0; i < viewsInDB.Length; ++i)  
    {  
        using (SqlCommand cmd = CreateSqlCommand(String.Format("SELECT id FROM sysobjects WHERE ID = OBJECT_ID('{0}.dbo.{1}') AND (type = 'V')", DatabaseName,viewsInDB [i])))  
        {  
            using (DataTable objects = ExecuteDataTableQuery(cmd))  
            {  
                if (objects.Rows.Count == 0)  
                {                              
                    return false;                              
                }  
            }  
        }  
    } 
    return true; 
}

The functions that are called from IsAllviewsExists : (Pleas note that they assume you have a data member of a connection called _conn)

SqlCommand CreateSqlCommand(string sql, SqlParameterCollection parameters)
{              
    SqlCommand cmd = _conn.CreateCommand();
    cmd.Connection = _conn;                

    cmd.CommandText = sql;

    if (parameters != null)
        foreach (SqlParameter param in parameters)
                    cmd.Parameters.Add(param); 
    return cmd;
}

DataTable ExecuteDataTableQuery(SqlCommand cmd)
{            
    DataTable table = null;

    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    {
        table = new DataTable();
        try
        {    
             adapter.Fill(table);    
        }
        catch (SqlException sqlEx)
        {
            rethrow;
        }    
    }    
    return table;
}

Upvotes: 1

Related Questions