I've always had good luck with this query[/url], though I've never compared the results or performance to the Jonathan Kehayias query.

That didn't work either. Thankfully the archival process is confused enough that there was another copy of the mdf and ldf sitting around in another batch. :w00t:

If I had one, that's what I'd do. This was an old backup whose data got aged out after the mdf and ldf were moved to archive.

I'll check that...

We wouldn't be going to 2012 on these, I don't think. I took a look at things yesterday, and it's a little hard to judge because neither server currently even...

I was just doing something similar:

SELECT cp.objtype AS ObjectType,

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

st.TEXT AS QueryText,

qp.query_plan AS QueryPlan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE st.text like...

Hi John,

The three rows it returns aren't really of any concern. The logins are for running client specific SSIS packages, and have the appropriate DB as their default.

Thanks

Hi John,

Can you...

It looks to be so. Let me know if you'd like me to query differently.

SELECT name, permission_name, state_desc

FROM sys.database_principals dp

INNER...

I thought the exact same thing, but every login and role has permissions to the master database, and the master database permissions are open. Wild stuff.

No sir. That query comes back empty.

They're connecting to master, which I promise, regular swear, and pinky swear exists. I'm not seeing these even show up in the login failure report. Dang.

In order:

Made no difference.

Public is wide open.

Thanks

Yep, that's the story. Also, if I assign another role (so they're under public and setupadmin, or public and serveradmin), they can't log in.

The default db is master for...

Beatrix Kiddo (4/11/2014)

---

Posted too soon!

They log into SSMS as DomainName\Username yes? If you check under Security > Logins > DomainName\Username right-click, Properties, what have they got under Server Roles?...

I know what you're getting at, and yes, the they're added as/mapped to Windows AD...