Enjoy our latest crossword puzzle and see if you can solve all the clues. These are all related to security in SQL Server. The key will be posted in the comments tomorrow and a word document version is attached below.
SQL Server Security
2. Second highest principal in SQL Server.
4. Turned off when a database is attached
11. Not NTLM
12. Not even the sysadmin's can see this data.
16. Protocol used when connecting from the server SQL is on.
17. Pretending to be someone else
19. One SQL instance talking to another.
20. Security trump
22. Contains 0 or more windows users.
24. Uses the private key to encrypt and decrypt data.
25. Used to contain a windows id and password.
1. Add a permission
3. Permission required to create stored procedures.
5. Required for all encryption in SQL Server
6. One way encryption.
7. This database is isolated from other databases.
8. Used to add a John Hancock to a stored procedure.
10. Alias for a credential
14. Uses both a public and private key.
18. Add random data to the string before encrypting it.
Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free
The industry standard for comparing and deploying SQL Server database schemas
Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial
This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. More »
The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Carlo Romagnano):
From BOL "Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE". In fact, the first batch returns error: Msg 208, Level 16, State 0, Line 4 Invalid object name '#t'.
What does the second batch return? (select 2)
-- first batch
drop table if exists #t
execute ('create table #t (i int, z int)')
select * from #t
-- second batch
drop table if exists #t
create table #t (i int)
execute ('alter table #t add z int')
select * from #t
The temporary table can be referenced by any nested batches or stored procedures.
Here an example of how to read any CSV into a temporary table and building it dynamically with the proper columns. Assume that the first row contains columns name.
IF OBJECT_ID('tempdb.dbo.#csv') IS NOT NULL
DROP TABLE #csv
-- load of first row with fieldnames
CREATE TABLE #csv (txt VARCHAR(MAX))
BULK INSERT #csv
WITH(FIELDTERMINATOR = '\t',LASTROW = 1) -- adjust the FIELDTERMINATOR option
-- build the dynamic table
IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t(filler INT) -- base of the temp table
DECLARE @sql VARCHAR(MAX)
,@tbl VARCHAR(MAX) = 'ALTER TABLE #t ADD '
,@comma CHAR(1) =''
SELECT @sql = txt FROM #csv
SELECT @tbl += @comma
+ '['+ Item +'] VARCHAR(MAX)' --
,@comma = ','
FROM master.dbo.DelimitedSplit8K(@sql,CHAR(9)) -- google DelimitedSplit8K to see function
ORDER BY ItemNumber
PRINT @tbl -- debug
EXECUTE(@tbl) -- alter table
ALTER TABLE #t DROP COLUMN filler
BULK INSERT #t
WITH(FIELDTERMINATOR = '\t',FIRSTROW = 2) -- adjust the FIELDTERMINATOR option
SELECT * FROM #t
- I am getting the following error when adding a new subscription.
.OLE DB provider "SQLNCLI11" for linked server "REPLLINK_RIPNCDB1-268974458_PNC60785638_PUBPNC_COM-1026461326_PNC60785638" returned message...
Removing records from SELECT
- Hi everyone
I have a table in SQL called dbo.Orders
The table has the following columns:
When a new order has been made...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.