Monday, January 21, 2008

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')

Sunday, January 20, 2008

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

Saturday, January 19, 2008

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

Thursday, January 17, 2008

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

Wednesday, January 16, 2008

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 ’

Tuesday, January 8, 2008

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;

Saturday, January 5, 2008

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

Friday, January 4, 2008

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

Thursday, January 3, 2008

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