June 27, 2007 at 3:05 pm
I'm trying to look at the user mapping for a login. It takes around 5 minutes to return results. I ran a trace while doing it and found the statement which has the longest duration:
SELECT
u.name AS [Name],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
ISNULL(u.default_schema_name,N'') AS [DefaultSchema]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = N'CO'
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K'))and(ISNULL(suser_sname(u.sid),N'')=N'ctcraig')
If I run it by hand it comes back in no time.
Has anyone experience this? It has been happening on 2 new 64 bit 2005 installs we have done.
thanks
sam
June 28, 2007 at 9:09 am
In which databases are you running it? What was the duration of the query in the SQL trace?
SSMS is running the query on every database. How many databases are on the server? Was it slower on one database than it was on the others?
June 28, 2007 at 10:33 am
I was just pressing the 'user mapping' page - which would then bring up a listing of databases. So I wasn't running it on a particular db.
Duration ranged between 0 and 41 seconds.
It executed quickly for all system dbs, and slowly on a few user databases. It consitently runs slow on specific dbs. I checked out the execution plan for one of them and most of the time is taken up on a clustered index on 20 rows and a hash match on 1.
We have 13 user databases on the server right now - should be around 130 in the next year.
Thanks for your help.
June 28, 2007 at 9:59 pm
How many rows are returned by the query when you run from Management Studio Query window?
What is the output of below queries on "problem" database?
select
count(*) from sys.database_principals
select
count(*) from sys.database_permissions
July 12, 2007 at 2:58 pm
Balmukund,
Thanks for your reply.
Here are results from one of the slow dbs.
-----------
82
(1 row(s) affected)
-----------
3403
(1 row(s) affected)
July 13, 2007 at 8:40 am
Are you still granting permissions on an object level? In SQL 2000, to grant someone execute on all procedures, you had to grant it for every procedure, but in SQL 2005, you have the option of granting blanket Execute permissions without specifying objects.
July 13, 2007 at 10:06 am
It looks to me like we are from the queries below. Previously the lead developer on this system was granting permissions. We're moving to a new server and this will no longer be the case.
select
count(*) from sys.database_permissions
select
count(*) from sys.database_permissions where class_desc = 'OBJECT_OR_COLUMN'
-----------
3424
(1 row(s) affected)
-----------
3382
(1 row(s) affected)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply