Recently we had a post by a reader stating that with 2000 tables in a

database it was taking 45 seconds to login it. He also indicated that adding

them to the db_reader role made the problem go away. To see the entire thread

(which is so far unresolved), follow this link:

Poor login speed with 2000+ tables

This seems like a good thread to explore. At first glance I'd expect

Enterprise Manager to be slow because it would need to load first all 2000

tables, then select the permissions on all 2000 objects when you go to view

permissions on a user. It's not slow to select 2000 rows out of sysobjects, but

DMO is object based, so you lose most of the advantages of doing set

based work as it loads one object at a time.

But just to log in to the db? The reader indicates that a different owner

owns the objects, so maybe some overhead from having to follow all the ownership

chains?

From what I can see in Profiler, if I connect as a user to master and then

change to a test database, the only procedure that affects security is

sp_MShasdbaccess. Looking at that proc, we see it's not very complicated:

;select name as 'dbname',
owner = substring(suser_sname(sid), 1, 24),
DATABASEPROPERTY(name, N'IsDboOnly') as 'DboOnly',
DATABASEPROPERTY(name, N'IsReadOnly') as 'ReadOnly',
DATABASEPROPERTY(name, N'IsSingleUser') as 'SingleUser',
DATABASEPROPERTY(name, N'IsDetached') as 'Detached',
DATABASEPROPERTY(name, N'IsSuspect') as 'Suspect',
DATABASEPROPERTY(name, N'IsOffline') as 'Offline',
DATABASEPROPERTY(name, N'IsInLoad') as 'InLoad',
DATABASEPROPERTY(name, N'IsEmergencyMode') as 'EmergencyMode',
DATABASEPROPERTY(name, N'IsInStandBy') as 'StandBy',
DATABASEPROPERTY(name, N'IsShutdown') as 'ShutDown',
DATABASEPROPERTY(name, N'IsInRecovery') as 'InRecovery',
DATABASEPROPERTY(name, N'IsNotRecovered') as 'NotRecovered'
from master.dbo.sysdatabases
where has_dbaccess(name) = 1
order by name

Of course we can't see what the has_dbaccess function is doing (or at least

I'm not profiling the correct events). So...it would seem the thing to do is try

to reproduce the problem. I created 500 tables using the following code. You can

download the complete solution file here. This is

VB.Net 2003 code, but overall it's very much like what you would do using VB6

other than the error handling. Even with 500 tables I saw no noticeable delay

when switching to the database via Query Analyzer.

Sub CreateTables(ByVal HowMany As Integer, ByVal UseRoleForTesting As Boolean)

        Dim oserver As SQLDMO.SQLServer
        Dim odb As SQLDMO.Database
        Dim oTable As SQLDMO.Table
        Dim oCol As SQLDMO.Column
        Dim J As Integer
        Dim oLogin As SQLDMO.Login
        Dim bLoginFound As Boolean
        Dim oUser As SQLDMO.User
        Dim oRole As SQLDMO.DatabaseRole


        Const TBL_NAME As String = "TEST"

        Try

            'connect to local using a trusted connection
            oserver = New SQLDMO.SQLServer
            oserver.LoginSecure = True
            oserver.Connect(".")

            'drop previous test db if it exists
            For Each odb In oserver.Databases
                If odb.Name = DB_NAME Then
                    oserver.KillDatabase(DB_NAME)
                End If
            Next

            'create a testing database
            odb = New SQLDMO.Database
            odb.Name = DB_NAME
            oserver.Databases.Add(odb)

            'add a test login if it doesnt exist
            For Each oLogin In oserver.Logins
                If oLogin.Name = DB_NAME & "USER" Then
                    bLoginFound = True
                    Exit For
                End If
            Next
            If bLoginFound = False Then
                oLogin = New SQLDMO.Login
                oLogin.Name = DB_NAME & "USER"
                oLogin.SetPassword("", PASSWORD)
                oserver.Logins.Add(oLogin)
            End If

            'clunky, but repeat for the object owner
            bLoginFound = False
            For Each oLogin In oserver.Logins
                If oLogin.Name = DB_NAME & "USEROBJ" Then
                    bLoginFound = True
                    Exit For
                End If
            Next
            If bLoginFound = False Then
                oLogin = New SQLDMO.Login
                oLogin.Name = DB_NAME & "USEROBJ"
                oserver.Logins.Add(oLogin)
            End If

            'add user to db
            oUser = New SQLDMO.User
            oUser.Name = DB_NAME & "USER"
            oUser.Login = DB_NAME & "USER"
            odb.Users.Add(oUser)

            'add user to db
            oUser = New SQLDMO.User
            oUser.Name = DB_NAME & "USEROBJ"
            oUser.Login = DB_NAME & "USEROBJ"
            odb.Users.Add(oUser)

            'add role, then add user to role
            oRole = New SQLDMO.DatabaseRole
            oRole.Name = "SSCTESTROLE"
            odb.DatabaseRoles.Add(oRole)
            oRole.AddMember(DB_NAME & "USER")

            'create a lot of tables
            For J = 1 To HowMany

                'name the tables sequentially
                oTable = New SQLDMO.Table
                oTable.Name = TBL_NAME & J.ToString
                oTable.Owner = DB_NAME & "USEROBJ"

                'just do a simple one column table
                oCol = New SQLDMO.Column
                oCol.Datatype = "int"
                oCol.Identity = True
                oCol.Name = "TestColumn"
                oTable.Columns.Add(oCol)
                odb.Tables.Add(oTable)

                If UseRoleForTesting = True Then
                    oTable.Grant(SQLDMO.SQLDMO_PRIVILEGE_TYPE.SQLDMOPriv_Select, "SSCTESTROLE")
                Else
                    'grant access to our test user
                    oTable.Grant(SQLDMO.SQLDMO_PRIVILEGE_TYPE.SQLDMOPriv_Select, DB_NAME & "USER")
                End If

            Next

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        Finally

            oserver.DisConnect()
            oserver = Nothing

        End Try

It's been a while since we've posted any DMO code, this combines a lot of

different objects to give you a basic framework for creating test environments.

To use it, you just call it like this:

 CreateTables(500, True)

So, I'm stumped. Is hardware masking the problem? Maybe I haven't recreated

the situation exactly enough to see the problem? What's the largest number of

tables you've worked with and have you noticed similar issues? Where would you

look next to solve the problem?