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
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.
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?
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.
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.
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.
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.
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')
DROP TRIGGER StopAndNotifyDDL
CREATE TRIGGER StopAndNotifyDDL
FOR DDL_DATABASE_LEVEL_EVENTS -- Captures all events in the database
Database DDL Trigger by Julian Kuiters 2005
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)', '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
-- Rollback the DDL changes
-- Get the user that attempted the change
SET @UserName = @xmlEventData.value( '(/EVENT_INSTANCE/LoginName)', 'nvarchar(max)' );
-- Get the database that the change was to affect
SET @DatabaseName = @xmlEventData.value( '(/EVENT_INSTANCE/DatabaseName)', '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)
+ '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.
@profile_name = 'Server SQLiMail Profile'
, @recipients = 'email@example.com'
, @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)
LOG, -- Make sure this error is in the SQL Log
NOWAIT, -- Return the error now
SETERROR; -- Sets @@ERROR and ERROR_NUMBER
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.
EXECUTED BY: julian
----- BEGIN DDL Statement --------------
create table tablec ( col1 int)
----- END DDL Statement ----------------
create table tablec ( col1 int)
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 ----------------
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:
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)