Email Article To a Friend View Printable Version

Q&A: Can I use system procedures like sp_help with a schema bound object name ?

SQL Server 2005

Q: Can I use system procedures like sp_help with a schema bound object name ?

When I run exec sp_help HumanResources.Resumes I get the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

A: Yes you can.
You need to quote the object name as in:

exec sp_help 'HumanResources.Resumes'



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz




Email Article To a Friend View Printable Version

Q&A: Can you BCP out a #Temporary table?

SQL Server 2005

Q: If I create a temporary table with:

SELECT OrderID,OrderDate,Total
INTO #TempOrders
FROM Invoices
Can I then BCP the data into a file with ?:
...
SET @cmd = 'bcp "#TempOrders" out "C:TempOrders.txt" -T
EXEC xp_cmdshell 'bcp ' +

A: No.

Because BCP will create a new connection, it will not be able to see the local temporary table.

To quote from SQL Server BOL - CREATE TABLE:

Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

Assuming your query was too complex to use with BCP; you could create a global temp table using the double number sign (##table_name) which can be accessed by BCP if it uses the same credentials as the table creator.

But if you really want to make sure that BCP can access your table, create an actual table in tempdb (SELECT OrderID,OrderDate,Total INTO tempdb.dbo.TempOrders FROM Invoices) then just add a DROP TABLE tempdb.dbo.TempOrderscommand after you BCP.

Or you could get BCP to execute a stored procedure that would create the temporary table and return the results.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Implementing Row and Cell Level Security in Classified Databases Using SQL Server 2005

SQL Server 2005

While SQL Server controls access to tables and objects on a user-by-user basis; restricting a user to see only certain rows in table is a common need.

You might set permissions to allow users to see Orders in a e-commerce system, but you will want to prevent them from seeing orders by other users. If your database held more than one companies orders, you would want to prevent companies from seeing each others orders.

Typically your application layer would implement this requirement of restricting the data a particular user could see. But wouldn't it be nicer if it was done in the database layer so users had (potentially) better access to data?

Microsoft has published a nice article on Implementing Row and Cell Level Security in Classified Databases Using SQL Server 2005. This is a really in depth article with code and examples covering the implementation of row and cell based security for groups of users, including encryption.

Reminds me of how SQL Server 2005 itself now prevents users from seeing objects they do not have permission to in the system tables. Eg: select * from master.sys.databases will only show you the databases you have access to, NOT necessarily all the databases on the server. The same goes for all the other system views in SQL Server 2005.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Free Online Training for SQL Server 2005 from Microsoft

SQL Server 2005

As part of Microsoft's push to improve dba/developer knowledge and uptake of SQL Server 2005, Microsoft have released free training for SQL Server 2005 available online until 1st November 2006.

There's currently seven free courses covering installation, administration, programming and other features of SQL Server 2005. Included in each course are hands-on virtual labs, and courses can be downloaded for offline study.

To help guage your current knowledge of SQL Server 2005, there is also anonline skill assessment.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 June CTP Released

SQL Server 2005

Today sees Microsoft release the June Community Technology Preview (CTP) of SQL Server 2005. It's a public preview and can be downloaded by anyone (you don't need a MSDN subscription for this one)

By the same note, Microsoft also announced at Tech.Ed that they will be shipping SQL Server 2005 and Visual Studio 2005 in Noveber 2005. That's a late "2005" product launch, but with such a large number of improvements and additions to the products, it's hardly surprising.

If you haven't had a taste of SQL Server 2005 yet, nows your chance.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server Health and History Tool (SQLH2) v2.026 Released

SQL Server 2005

For those of us useing the SQL Server Health and History Tool (a.k.a. SQLH2) version 2.026 was released on 19 May 2005.

A free download from Microsoft, the SQL Server Health and History Tool (SQLH2) collects information about your SQL Server installations. Details about which services/features are installed; configuration of the machine, OS and SQL Server; database size and objects; and additionally it can track Perfomance Counters.

Pre-built reports for Reporting Services are also available. Making it easy to view your existing configuration, and if you setup SQLH2 to collect information regularly, you will have a history of changes.

This new version, also has better documentation of the SQLH2 database, so you can build your own reports. Search microsoft.com for "SQLH2_Repository_Documentation.zip"

If you're not already using this tool to monitor the history of your SQL Server installations, I'd seriously recommend checking it out. Run it once a day (or more if you collect a lot of performance data) for a month, and check out the tracking it can show.

The performance stats can take a little space (use the SQL Datasizer Tool to predict how much space will be needed) but I'm sure an Analysis Cube would suffice for long term tracking.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

DDL Trigger - Prevent and Notify DDL Changes

SQL Server 2005

SQL Server 2005 allows you to create DDL Triggers in a database, that fire whenever someone attempts to change the structure of your database.

Ever accidentally created tables / views / sprocs in the master database? Well Greg Low published a trigger you can place in the master database to prevent changes there. Apparently Greg's original version of the trigger was a show stopper. After he applied it the first time, there was no way to undo it. Attempting to disable the trigger fired the trigger, which rolled back the disable. The version you see on his site now uses a CHARINDEX to see if you are attempting to change the trigger itself, so that you can disable the trigger when you need to. A few people noted that you can hack around this trigger. Because of the use of CHARINDEX(...) = 0 you can still apply changes by hiding -- ProtectMasterDDL in your DDL command.

Darshan Singh has posted a update to the trigger, which uses less code, and also grabs the original user and DDL statement from the EVENTDATA() It doesn't however protect or prevent triggers, so you can still cause some damage.

From a security standpoint. I think you not only want to prevent people from mucking up your master database, you need to report it to someone. Raising an error that is logged in the SQL Logs is vital. As well as notifying your DBA / System Teams. This sort of thing could be used as an early detection of an unwanted intruder (or just a wayward developer).

So here is the DDL trigger I've crafted. You can use it in any database. You will need SQLiMail installed in the MSDB database if you want email notifications (comment out the mail section if you don't want emails sent). Don't forget to change the @profile_name and @recipients so that you'll get the emails.

IF EXISTS (SELECT name FROM sys.triggers
       WHERE parent_class = 0 AND 
       name = 'StopAndNotifyDDL')
BEGIN 
  DROP TRIGGER StopAndNotifyDDL
  ON DATABASE;
END;
GO 

CREATE TRIGGER StopAndNotifyDDL
ON DATABASE
FOR  DDL_DATABASE_LEVEL_EVENTS  -- Captures all events in the database
AS
BEGIN

    /*
        Database DDL Trigger by Julian Kuiters 2005
        http://www.juliankuiters.id.au
        Version 1.1 25 May 2005
        Use this trigger to prevent changes to a database (your master database for example), and to
        receive email notifications when a DDL statement is stopped.
    */

    SET NOCOUNT ON;                         -- Prevents the trigger from creating another record set

    DECLARE @xmlEventData XML;              -- To hold EVENTDATA() xml output
    DECLARE @DDLStatement nvarchar(max);    -- To hold the DDL statement that was attempted
    DECLARE @UserName     nvarchar(max);    -- To hold the user that execute the command
    DECLARE @DatabaseName nvarchar(max);    -- To hold the database that was trying to be changed
    DECLARE @ErrorMsg     nvarchar(max);    -- To hold the error message we create
    DECLARE @MailSubject  nvarchar(2048);   -- To hold the Subject for the Mail.

    SELECT @xmlEventData = EVENTDATA();     -- Get the EVENTDATA()

    -- Get the DDL Statement that was attempted from the eventdata.
    SET @DDLStatement = @xmlEventData.value( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' );

    -- If the DDL Statement is trying to make changes to anything other than this trigger
    IF CHARINDEX('DROP TRIGGER StopAndNotifyDDL',@DDLStatement) != 1
        AND CHARINDEX('ALTER TRIGGER StopAndNotifyDDL',@DDLStatement) != 1
    BEGIN

        -- Rollback the DDL changes
        ROLLBACK;

        -- Get the user that attempted the change
        SET @UserName = @xmlEventData.value( '(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)' );

        -- Get the database that the change was to affect
        SET @DatabaseName = @xmlEventData.value( '(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)' );

        -- Create the Error Message
        SELECT @ErrorMsg  =   'DATABASE:    ' + @DatabaseName + CHAR(13) 
                            + 'EXECUTED BY: ' + @UserName + CHAR(13) 
                            + '----- BEGIN DDL Statement --------------' + CHAR(13) + CHAR(13) 
                            + @DDLStatement + CHAR(13) + CHAR(13) 
                            + '----- END DDL Statement ----------------' + CHAR(13) + CHAR(13) 
                            + CAST(@xmlEventData as nvarchar(max));
        SELECT @MailSubject = 'StopAndNotifyDDL Trigger caught DDL Statement on ' + @DatabaseName;

        -- Print the message to the user
        PRINT 'StopAndNotifyDDL Trigger has prevented your statement from running.' + CHAR(13) 
               + 'You must disable the StopAndNotifyDDL trigger before you can make these changes.' + CHAR(13) 
               + @ErrorMsg + CHAR(13) 
               + CHAR(13) 
               + 'Notifying Administrators...';


        -- Send an email
        -- NOTE: You need SQLiMail installed in the MSDB database.
        -- Remember: Change the @profile_name and @recipients to suit your environment.
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'Server SQLiMail Profile' 
            , @recipients   = 'dba@mydomian.x'
            , @subject      = @MailSubject
            , @body         = @ErrorMsg  
            , @importance   = 'high'; 
  

        -- Raise an error
        SET @ErrorMsg = REPLACE(@ErrorMsg,CHAR(13),' ')                   -- Remove carriage returns before raiserror 
        RAISERROR ( 'StopAndNotifyDDL Trigger: %s', 18, 1, @ErrorMsg) 
        WITH 
            LOG,        -- Make sure this error is in the SQL Log
            NOWAIT,     -- Return the error now
            SETERROR;   -- Sets @@ERROR and ERROR_NUMBER

        

    END
END;
GO

Once you have applied the trigger to a database, any DDL statement should produce output similar to the following (XML has been formatted to make it easier to read):

StopAndNotifyDDL Trigger has prevented your statement from running.
You must disable the StopAndNotifyDDL trigger before you can make these changes.
DATABASE:    scratch
EXECUTED BY: julian
----- BEGIN DDL Statement --------------

create table tablec ( col1 int)

----- END DDL Statement ----------------


	CREATE_TABLE
	2005-05-23T11:40:25.203
	58
	SQL05
	julian
	julian
	scratch
	dbo
	tablec
	TABLE
	
		
		create table tablec ( col1 int)
	

Notifying Administrators...
Mail successfully queued. No user action is required.
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.
Msg 50000, Level 18, State 1, Procedure StopAndNotifyDDL, Line 68
StopAndNotifyDDL Trigger: DATABASE: scratch EXECUTED BY: julian ----- BEGIN DDL Statement -------------- create table tablec ( col1 int) ----- END DDL Statement ----------------



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Setting your default database to tempdb

SQL Server 2005

Personally I like to set the default database for all logins to tempdb. It helps people remember to select the correct database before they start doing things. If someone forgets to change their database, i'd rather them attempting to do crazy things in tempdb instead of master.

There are some programs / install scripts that don't handle having a default database that is not master. (Reporting Services install in SQL 2005 SP2 suffered this problem). If your app or install has troubles when the default database is set to tempdb, use SQL Profiler to see if the problem is because the app is expecting the default to be master. If so set the user back to master; run the tasks; and set them back to tempdb as soon as you can.

Set a default database when creating a login in SQL Server 2005:

CREATE LOGIN [MYDOMAINASqlUser]
FROM WINDOWS
WITH DEFAULT_DATABASE = [tempdb]

Change the default database of an existing login in SQL Server 2005 with:

ALTER LOGIN [MYDOMAINASqlUser]
WITH DEFAULT_DATABASE = [tempdb]

To change the default database of an existing login in SQL Server 2000 you would use:

exec sp_defaultdb @loginame = 'MYDOMAINASqlUser', @defdb = 'tempdb'



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Go home early with WAITFOR

SQL Server 2005

It's late at night, you're still at work, waiting for long running task to complete so you can go home. Well why not make SQL Server 2005 wait for you?

Well gather your things and make use of the new WAITFOR statement, thats been introduced as part of Service Broker. You can ask SQL Server to wait until a certain time of the day, or until an amount of time passes, or you can wait for a message from a queue.

--Wait until 9:00 PM
WAITFOR TIME '21:00' --Wait for 22 Minutes to pass
WAITFOR DELAY '00:22:00' --Wait for a message to be recieved from a Service Broker messaging queue
WAITFOR (RECEIVE * FROM ProcessQueue)

Damn nifty! ... I'm going home. Till tomorrow!



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz







Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.