CirquedeSQLeil (9/27/2010)

---

Here is how you can now confirm whether or not the permissions granted have been applied:

Use master

GO

SELECT sp.permission_name, p.name

FROM sys.server_permissions sp

Inner Join sys.server_principals p

On p.principal_id = sp.grantee_principal_id

Where sp.permission_name =...

homebrew01 (9/27/2010)

---

So maybe it does work ? ... just can't verify it with sp_helprotect ?

Is there a way to see after the fact what was done ?

I'll have to test...

Yes, functionally it is the same.

The article is worded poorly..it says VIEW DEFINITION was...

You probably won't see it at the database level because it's a server-level permission. Also, I believe that was new in 2005, so it won't show up there. You'd have...

He/she did say it was a clustered PK back on page 2 or so..but just in case it wasn't, thats why I was suggesting deleting with a table lock. SQL...

Try deleting with (tablock) also, when you get a chance.

Or better yet, drop the indexes so the table is a heap, then delete with tablock, then recreate the clustered index.

Just for kicks, try this:

Delete from tablename ( with tablock )

see if it changes the data size, which should be 0.

edit: I doubt we'd find anything out of the ordinary,...

The filestream pointers are pointing to the filegroup, rather than the actual path on the drive. The filegroup, like any other, can be pointed wherever you want when you restore...

Ok, I think there's a misunderstanding here.

Pretty much every spid will have a lock of some kind on something and will show up in sp_lock. Notice the DBID in yours...

I've never seen a table that stubborn before. Was hoping that would return different results than sp_spaceused, and it would have just been an issue with sizes not updating.

Have you...

Ignoring sp_lock for a second..

Run this:

SELECT SPID, login_time, loginame FROM Master.dbo.sysprocesses

where loginame = 'your username here'

Kill the spid that has the login time from last week.

Run this and see if there's an index using more space than the others (significantly, anyway). Replace the tablename_here with your table name

select

'['+DB_NAME(database_id)+'].['+c.name+'].['+d.name+']' as [DB.Table]

,b.name as [Index Name]

,page_count /...

Are you sure you're trying to kill the right spid? You only get that error when you try to kill the spid that you're currently executing from..

Log in using a different login that has access to kill processes? If you're using your domain account, log in as SA or vice versa.