SQL Server Performance and permissions

[ralphm1]

Hello,

I am running SQL Server 2000 standard in mixed mode security and have
two problems.

1.) I created a database as sa and assigned a login as db_owner,
however, the design view is grayed out for all tables. All tables are
owned by dbo and no user defined role exists for the database.

2.) Any login other than sa will time-out in Enterprise Manager or take
extremely long. The sa login performs acceptably well. Once the non
sa login is connected to a server in EM, the database list shows 'no
items'. F5 refresh takes incredibly long. Accessing the tables under
a DB often times out.

Any ideas?

Mike

[Simon Hayes]

<ra*****@mail.horacemann.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Hello,

I am running SQL Server 2000 standard in mixed mode security and have
two problems.

1.) I created a database as sa and assigned a login as db_owner,
however, the design view is grayed out for all tables. All tables are
owned by dbo and no user defined role exists for the database.

2.) Any login other than sa will time-out in Enterprise Manager or take
extremely long. The sa login performs acceptably well. Once the non
sa login is connected to a server in EM, the database list shows 'no
items'. F5 refresh takes incredibly long. Accessing the tables under
a DB often times out.

Any ideas?

Mike

I couldn't reproduce your first problem - I added a login, put it in the
db_owner role of a database, and the "Design Table" menu option is available
for all tables; if the user is not in db_owner it is greyed out, so perhaps
you didn't add the user correctly? You can try these queries to confirm if
the user is in the role:

select is_member('db_owner')
exec sp_helprolemember 'db_owner'

As for the second issue, you could try increasing the query timeout in EM
(Tools - Options - Advanced), but I don't know if that will help. One point
to mention is that if your databases are set to auto close, then it can take
a long time to display them all in EM. But this isn't on by default for
Standard Edition, and you say that you get timeouts working on tables too,
so it seems unlikely. It might still be worth checking, though:

select name, databasepropertyex(name, 'IsAutoClose')
from master..sysdatabases

If this doesn't help, I suggest you give some more details - how many
databases, do you get similar timeouts from Query Analyzer or other clients,
do both Windows and SQL logins have this problem, are you connecting over a
LAN or a WAN, are there any general network issues etc.

Simon

[ralphm1]

Simon,

Thank you for your reponse.

1.) I had tried this query and the login shows up as db_owner. I could
not reproduce the problem on other servers either.

2.) The problem is with EM only with a NON-sa login. The sa login
performance is fine. Any NON-sa login performs poorly or stops
responding. Is the sa login NOT affected by IsAutoClose or somehow
treated differently than a NON sa?

Thanks
Mike

[Erland Sommarskog]

(ra*****@mail.horacemann.com) writes:

2.) The problem is with EM only with a NON-sa login. The sa login
performance is fine. Any NON-sa login performs poorly or stops
responding. Is the sa login NOT affected by IsAutoClose or somehow
treated differently than a NON sa?

Yes. For sa Enterprise Manager does not have to check whether you have
permissions to access a certain database - sa always have permission.
But for a non-sa user, EM submits a query foreach database when you
expand the database node. If databases are in autoclose, then this will
take a long time.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ralphm1]

Autoclose is set to 0 for all databases. Is that why the database list
will return 'No items' without a refresh?