I have pasted a method I wrote, which is too slow to my liking (easily takes 60+ seconds to execute). The method loops through 12 different servers, checks if userX has access to any of the databases in this server and reports the result. I'm already using a table to save all of this information. That information is already stored. This method is supposed to check if the records in that table and the reality match. If not, report back any differences. public string showMissingDatabaseInfo(){    MyUser user = (MyUser)Session[CurrentUser];    MyFramework.Database myDB = new MyFramework.Database();    List<LoginDatabase> listExistingDBpermsOfUser = dbRepos.getExistingDBpermissionsByUser(user.LoginID);    List<string> listMissingDBpermsOfUsers = new List<string>();    StringBuilder result = new StringBuilder();    List<SqlParameter> myParams = new List<SqlParameter>();    for (int teller = 13; teller <= 25; teller++)    {        //The brackets, [ ], are essential in the query below. Otherwise the processor will be confused with certain DB names        string qryAboutToBeExecuted = DECLARE @DBuser_sql VARCHAR(4000) + Environment.NewLine +                    DECLARE @DBuser_table TABLE([DBName] VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500)) + Environment.NewLine +                    SET @DBuser_sql = 'SELECT DISTINCT ''[?]'' AS [DBName],[?].[sys].[database_principals].[name] AS Name,[?].[sys].[database_principals].[type_desc] AS LoginType FROM [?].sys.database_principals  + Environment.NewLine +                    WHERE [?].sys.database_principals.sid NOT IN(0x01, 0x00) AND [?].sys.database_principals.sid IS NOT NULL AND [?].sys.database_principals.type NOT IN(''C'') AND [?].sys.database_principals.is_fixed_role <> 1  + Environment.NewLine +                    AND [?].sys.database_principals.name NOT LIKE ''##%'' AND ''[?]'' NOT IN (''master'',''msdb'',''model'',''tempdb'') AND Name LIKE '' + user.LoginUsername + '' ORDER BY Name' + Environment.NewLine +                    INSERT @DBuser_table + Environment.NewLine +                    EXEC sp_MSforeachdb @command1 = @dbuser_sql + Environment.NewLine +                    SELECT * + Environment.NewLine +                    FROM @DBuser_table + Environment.NewLine +                    ORDER BY [DBName];        try        {            DataSet ds = myDB.executeResultDataset(DBServer + teller, qryAboutToBeExecuted, myParams);            foreach (DataRow row in ds.Tables[0].Rows)            {                try                {                    string currentDBnaam = row[ds.Tables[0].Columns[0]].ToString();                    LoginDatabase testDB = listExistingDBpermsOfUser.Where(c => c.DBName.Equals(currentDBnaam)).FirstOrDefault();                    if (testDB != null && !String.IsNullOrWhiteSpace(testDB.DBName))                    {                        listExistingDBpermsOfUser.Remove(testDB);                    }                    else                    {                        listMissingDBpermsOfUsers.Add(currentDBnaam);                    }                }                catch (Exception)                {                }            }        }        catch (Exception)        {        }    }    result.Append(<div>);    int totalForMissingDBperms = listMissingDBpermsOfUsers.Count;    int totalForExistingDBperms = listExistingDBpermsOfUser.Count;    if (totalForExistingDBperms + totalForMissingDBperms == 0)    {        result.Append(The database permissions of the user in SQL Server Management Studio and the LoginDatabase table are perfectly synced and up to date.);    }    else    {        if (totalForMissingDBperms > 0)        {            result.Append(The user has access to the following databases, even though that's not specified below: <ul>);            foreach (string objMissingDB in listMissingDBpermsOfUsers)            {                result.Append(<li> + objMissingDB + </li>);            }            result.Append(</ul> <br />);        }        if (totalForExistingDBperms > 0)        {            result.Append(<br />The LoginDatabase table also indicates the user has access to the following tables, though in reality he/she doet not. <ul>);            foreach (LoginDatabase objLoginDB in listExistingDBpermsOfUser)            {                result.Append(<li> + objLoginDB.DBName + </li>);            }            result.Append(</ul>);        }    }    result.Append(</div>);    return result.ToString();}
Checking user access to servers
c#;performance;sql;sql server;database
Let's see...    string qryAboutToBeExecuted = DECLARE @DBuser_sql VARCHAR(4000) + Environment.NewLine +                DECLARE @DBuser_table TABLE([DBName] VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500)) + Environment.NewLine +                SET @DBuser_sql = 'SELECT DISTINCT ''[?]'' AS [DBName],[?].[sys].[database_principals].[name] AS Name,[?].[sys].[database_principals].[type_desc] AS LoginType FROM [?].sys.database_principals  + Environment.NewLine +                WHERE [?].sys.database_principals.sid NOT IN(0x01, 0x00) AND [?].sys.database_principals.sid IS NOT NULL AND [?].sys.database_principals.type NOT IN(''C'') AND [?].sys.database_principals.is_fixed_role <> 1  + Environment.NewLine +                AND [?].sys.database_principals.name NOT LIKE ''##%'' AND ''[?]'' NOT IN (''master'',''msdb'',''model'',''tempdb'') AND Name LIKE '' + user.LoginUsername + '' ORDER BY Name' + Environment.NewLine +                INSERT @DBuser_table + Environment.NewLine +                EXEC sp_MSforeachdb @command1 = @dbuser_sql + Environment.NewLine +                SELECT * + Environment.NewLine +                FROM @DBuser_table + Environment.NewLine +                ORDER BY [DBName];The first thing that comes to mind is.... why does this horrible string needs to be in the code?Make it a parameterized stored procedure and get this nastiness out of your sight!So that's the SQL part.StringBuilder result = new StringBuilder();Fair enough, you're building a string; a StringBuilder looks like the right tool for the job right?Wrong! Why are you building a string?result.Append(<div>);result.Append(</ul> <br />);result.Append(<li> + objLoginDB.DBName + </li>);Don't do that. I don't do much web development, but the little I've done has shown me that if you're building the HTML string output yourself, you're doing it wrong. Very wrong.Make the SQL a stored procedure, run the stored procedure from C#, get the results into a DataTable if you have to, or an IEnumerable<SomeComplexType> if you would rather use Entity Framework, and use whatever is appropriate to pass this model to the view.Performance-wise, this can't be helping:foreach (DataRow row in ds.Tables[0].Rows){    try    {Do whatever it takes to avoid throwing and catching exceptions in a loop. And you probably don't want to catch System.Exception here, but something much more specific, that you should avoid having thrown any way. If you're catching exceptions repeatedly in that loop, chances are that you have a very much avoidable bottleneck there.Catching System.Exception is one thing; swallowing it is another - now if anything goes wrong, you have no idea what happened, where, why, or how.This is just plain weird:result.Append(The database permissions of the user in SQL Server Management Studio and the LoginDatabase table are perfectly synced and up to date.);SSMS is just a client; the permissions are in the database, not in SSMS.