Querying SQL 2000 Table Permissions

Since I am auditing old sql 2000 instances, these methods are necessary

Method 1 : The appropriate information_schema view.

select 
 grantor, 
 grantee, 
 table_schema+'.'+table_name as objectname, 
 privilege_type 
from 
 information_schema.table_privileges

Method 2 : Capturing the output of sp_helpprotect
(nb : sp_helpprotect can be passed parameters - http://msdn2.microsoft.com/en-us/library/ms190310.aspx)

create table #helpprotect_results (
      owner sysname,
      object sysname,
      grantee sysname,
      grantor sysname,
      protecttype nvarchar(10),
      action nvarchar(20),
      columnname sysname )

insert into #helpprotect_results exec sp_helprotect

select grantor,
    grantee,
       owner + '.' + object as objectname,
       protecttype,
       action
from   #helpprotect_results
order by grantee

drop table #helpprotect_results

Method 3 : Good old system tables

select
 user_name(sec.grantor) as grantor,
 user_name(sec.uid) as grantee,
 stbl.name + '.' + obj.name as objectname,
 case obj.type
  when 'C' then 'Check constraint'
  when 'D' then 'Default (constraint or stand-alone)'
  when 'F' then 'Foreign Key constraint'
  when 'PK' then 'Primary Key constraint'
  when 'P' then 'SQL Stored procedure'
  when 'FN' then 'SQL scalar function'
  when 'R' then 'Rule (old-style, stand-alone)'
  when 'RF' then 'Replication-filter-procedure'
  when 'S' then 'System base table'
  when 'TA' then 'assembly (CLR) DML trigger'
  when 'TF' then 'SQL table-valued-function'
  when 'U' then 'Table (user-defined)'
  when 'UQ' then 'Unique constraint'
  when 'V' then 'View'
  when 'X' then 'Extended stored procedure'
 end as objtype,
 protecttype.name permission_level, 
 action.name as permission
from
 dbo.sysobjects as obj
inner join sysusers as stbl on stbl.uid = obj.uid
inner join sysprotects as sec on sec.id = obj.id
inner join master.dbo.spt_values as action on sec.action = action.number and action.type = 't'
inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = 't'
where 
 obj.type in ('U','V','P','FN','X') 

TSQL to Determine Index Fragmentation Level

Viewing index fragmentation -

SELECT OBJECT_NAME(OBJECT_ID) AS Tablename
,sysindexes.name AS Indexname
,index_type_desc
,avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
INNER JOIN sysindexes ON dmv.OBJECT_ID = sysindexes.id
AND dmv.index_id = sysindexes.indid

SQL 2005 : Column Alias in ORDER BY Clause

Found this today when migrating some views from 2000 to 2005.
You cannot use a column alias in ORDER BY Clause in SQL 2005.
The following works in sql 2000, but not sql 2005 -

SELECT LastName + ', ' + FirstName, TotalPurchaseYTD as Spend
FROM SalesIndividualCustomer Customer
INNER JOIN SalesIndividualDemographics Demographics
ON Customer.CustomerID = Demographics.CustomerID
ORDER BY Demographics.Spend DESC

To fix, replace the alias with the original column name -

SELECT LastName + ', ' + FirstName, TotalPurchaseYTD as Spend
FROM SalesIndividualCustomer Customer
INNER JOIN SalesIndividualDemographics Demographics
ON Customer.CustomerID = Demographics.CustomerID
ORDER BY Demographics.TotalPurchaseYTD DESC

Update , Jan 2011 , or as Nabeel points out in the comments - just don't use a table alias and column alias together in the ORDER BY clause.

SELECT LastName + ', ' + FirstName, TotalPurchaseYTD as Spend
FROM SalesIndividualCustomer Customer
INNER JOIN SalesIndividualDemographics Demographics
ON Customer.CustomerID = Demographics.CustomerID
ORDER BY Spend DESC

Adding server logins to current database

Loops login names and ;
1) grants database access
2) adds db_datareader role
3) adds db_datawriter role

Includes 2 login lists, either all or logins owning sql agent jobs (uncomment as appropriate)

DECLARE @sql NVARCHAR(MAX)
DECLARE @login VARCHAR(200)
DECLARE CURSORNAME CURSOR FOR
-- all logins >

  select [name] from master.dbo.syslogins
 where [name] <> 'sa'
 and [language] is not null  

-- logins that own jobs! >

-- SELECT logins.[name]
-- FROM msdb.dbo.sysjobs jobs
-- INNER JOIN master.dbo.syslogins logins
-- ON jobs.owner_sid = logins.sid

OPEN CURSORNAME
FETCH NEXT FROM CURSORNAME INTO @login

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'CREATE USER [dummyuser] FOR LOGIN [dummyuser]
   EXEC sp_addrolemember N''db_datareader'', N''dummyuser''
   EXEC sp_addrolemember N''db_datawriter'', N''dummyuser''  '

SET @sql = REPLACE(@sql,'dummyuser',@login)

print @sql
exec sp_executesql @sql
FETCH NEXT FROM CURSORNAME INTO @login
END

CLOSE CURSORNAME
DEALLOCATE CURSORNAME

GO

Changing Windows Password in Windows 2008 RDP Session

( because Alt-Ctrl-Del operates on the host!!! )

Start > ‘ Windows Security ’

Then you are presented with ‘ Lock / Log off / Change a password... / Task Manager ’

Vista Speedup tips

For the uninitiated...
http://www.neowin.net/forum/index.php?showtopic=513758

Investigating DB Designs...

Tables without Primary keys ;

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
    FROM sys.tables
    WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
   AND OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
    ORDER BY SchemaName, TableName;

Tables without indexes ;

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName 
 FROM sys.tables 
 WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0 
   AND OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
    ORDER BY SchemaName, TableName;

SQL 101 : Temporary Tables

CREATE TABLE #Table1 (ID INT, Name VARCHAR(50) )

There are 2 types of temporary table. Both are stored in the tempdb database.

Local Temporary Tables (prefix #)
Available to the current session (connection per user) only.
Deleted when the session ends (user disconnects).

Global Temporary Tables (prefix ##)
Available to the all connected sessions, after creation.
Deleted when all sessions that used the temp table disconnect.

Global Temp tables are especially useful when dealing with dynamic sql to pass data between sql sessions e.g. sp_executesql results sets.

Database Loop - WITHOUT CURSOR

SET NOCOUNT ON

DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT

DECLARE  @DatabaseName NVARCHAR(255)                   
SELECT @CurrentRow = 1
               
DECLARE  @Databases  TABLE(
 UNIQUEROWID  INT   IDENTITY ( 1,1 )   PRIMARY KEY   NOT NULL
,DATABASENAME NVARCHAR(255)
)
   
INSERT INTO @Databases (DATABASENAME)
SELECT NAME
FROM SYS.DATABASES
WHERE DATABASE_ID > 4
               
SELECT @TotalRows = COUNT(* )
FROM @Databases
 
WHILE @CurrentRow <= @TotalRows  
BEGIN    
 SELECT @DatabaseName = DATABASENAME      
 FROM @Databases     
 WHERE UNIQUEROWID = @CurrentRow         
 PRINT @DatabaseName         
 SELECT @CurrentRow = @CurrentRow + 1  
END 

Database Loop - Loop all user databases with a cursor

-- Cursors are bad kids! Don't use them ...
DECLARE @DatabaseName VARCHAR(200)
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM sys.databases where database_id > 4
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @DatabaseName
-- Do other stuff to databases here,
-- e.g DBCC CHECKDB(@Variable)
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Reporting of CPU Drift (SQL 2005 SP2)

From the sql logs of a standalone AMD Operteron 2218 server running SQL 2005 SP2.

" The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs. "

Really not much we can do about this.
The answer appears to be to prevent changes in CPU frequency by disabling the power saving options on the box.
Not a very GREEN solution, and not one conducive to saving money on data center power consumption.

http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx

http://support.microsoft.com/kb/931279