Create new SQL Server Login on Log shipping destination server

Create new SQL Server Login on Log shipping destination server
As restored database on destination server will be in standby read only mode, it is not possible to create a new login on it and then associate this login with newly restored database. If you require to create a login with specific permissions onto restored database you need to create a similar login on source server first and then onto destination server. Follow the steps below to create a new SQL Server login on log shipping destination server.
a. Create new SQL Server login on source machine and grant required database access permissions which you want this user to have on destination machine.
b. Create a user on source database on source machine from newly created login in step a.
c. Take a transaction log backup on source machine and transfer to destination server.
d. Create new SQL Server login on destination machine with same login name as of source server. Don’t give any database access grants to this login yet.
e. Restore the transaction log on destination server which was backed in step c. After successful restoration, you’ll see the new user created in step a in the database security user tab. But at this step, the new user will not be able to login and access the database.
f. Log on to source machine again and get the SID of SQL login created in step a. You can use the following query to get the SID of the login:

SELECT name, sid
FROM master.dbo.syslogins
WHERE name in ('testlogin1')
Note: ‘Testlogin1’ is the login name.

g. Now on destination server, drop the login created in step d. Use the following query to drop the login
sp_droplogin 'testlogin1'
go
Note: ‘Testlogin1’ is the login name.

h. Recreate the same login again but with same SID as of source machine.
sp_addlogin 'testlogin1', @passwd='pwd123', sid=0xA20F6D5qwer0891asdfB448380E66F5D8C6155
go
Note: ‘Testlogin1’ is the login name. ‘pwd123’ is the password for this login and ‘0xA20F6D5qwer0891asdfB448380E66F5D8C6155’ is the SID from source machine

i. Run a test to ensure newly created SQL login can successfully access the restored database.

Get date of next Sunday based on a date

If you need to find the date on next sunday based on a date value supplied, use the following query

SELECT DATEADD(wk,DATEDIFF(wk,0,cast('20081121' as datetime)+7),0)-1

Source: http://www.sqlservercentral.com/Forums/Topic606669-338-3.aspx

How to remove schema from database user

ALTER AUTHORIZATION ON SCHEMA::[db_securityadmin] TO [db_securityadmin]

Avoid Using NOT IN Clause. Instead use NOT EXISTS

Hi
It is just my experience and preference to use NOT EXISTS clause instead of NOT IN clause in SQL query. There is dramatic change in preformance when NOT EXISTS is used as compared to NOT IN.

I am running a query where i have to select all the records which are not present in other table. The execution plan shown for both queries explain the result itself. Due to confedentiality, i am only writing query cost of each run instead of displaying estimated execution plan.

NOT IN Query Cost - 88%
NOT EXISTS Query Cost - 12%

There is huge difference in there.

Get list of table names in each database

I came across a situation where i have to gather information about all tables in each database onto a particular server. There were nearly 60 databases each with more than hundred tables. It was impossible for me to run a query for every single database and then merge the results.

In order to achive this, i created a temporary table to store database and table names. With help of undocumented stored procedure 'sp_msForEachDB' i stored the results into temporary table.

CREATE TABLE #t(dbName SYSNAME, tblName SYSNAME);

EXEC sp_msForEachDB 'INSERT #t

SELECT ''?'', TABLE_NAME

FROM [?].INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE=''BASE TABLE'';';

SELECT * FROM #t ORDER BY dbName,tblName;

DROP TABLE #t;

In order to understand 'sp_msForEachDB' command, refer to post 'Traversing
through every database: sp_MSforeachdb'

Traversing through every database: sp_MSforeachdb

This is undocumented stored procedure created by Microsoft.

The SP "sp_MSforeachdb" is found in the "master" database. This SP is used to execute a single or multipel T-SQL statements against every database defined to a SQL Server instance.

Here is the syntax for calling this undocumented SP:

EXEC @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar,
@command2, @command3, @precommand, @postcommand

Where:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
@command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run against each database
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
@postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.

Example:
Lets say, we have to run "DBCC CHECKDB" command for each database onto server. Run the following code:



DECLARE @cmd1 varchar(500)
DECLARE @cmd2 varchar(500)
SET @cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
SET @cmd2 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
EXEC sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2

How can I find whether the SQL Server 2005 installed is a 64bit or 32bit?

Sometimes a situation arise when few features of SQL Server don't work on 64 bit version and a developer is not aware of which version he is using. In order to know about SQL Server version, you can use

SELECT @@version

This SQL statement will tell you the following information
- SQL Server Version
- Edition
- Processor family - 32 bit or 64 bit

Example is:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Find Nth Record from a table

Sometimes you have to find Nth record from a table in database. There is no such inbuilt function which could return this record. There could be many ways to find the result, I wrote a query which solves the purpose.

SELECT top 1 *
FROM (SELECT top Nth * FROM dim_store) as a
ORDER BY dim_store_id desc

SQL Server - Maximum Sizes

Object	Maximum sizes/numbers	Maximum sizes/numbers	Maximum sizes/numbers
	SQL Server 7 (32-bit)	SQL Server 2000 (32-bit)	SQL Server 2005 (32-bit)
Batch size	65,536 * Network Packet Size	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8000	8000	8000
Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column	231 -2 bytes/p>	231 -2 bytes/p>	231 -1 bytes/p>
Bytes per GROUP BY, ORDER BY	8060	8060	8060
Bytes per index	900	900	9001
Bytes per foreign key	900	900	??2
Bytes per primary key	900	900	900 - Not listed, but since this will be an index, the index guidelines should apply.
Bytes per row	8060	8060	8060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB or 128MB3	Lesser of batch size or 250 MB or 128MB3	128MB
Clustered indexes per table	1	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10	10
Columns per index	16	16	164 5
Columns per foreign key	16	16	164
Columns per primary key	16	16	164
Columns per base table	1024	1024	1024
Columns per SELECT statement	4096	4096	4096
Columns per INSERT statement	1024	1024	1024
Connections per client	Maximum value of configured connections (32,767 max)	Maximum value of configured connections (32,767 max)	Maximum value of configured connections (32,767 max)
Database size	1,048,516 terabytes	1,048,516 terabytes	1,048,516 terabytes
Databases per instance of SQL Server	32767	32767	32767
Filegroups per database	256	256	32767
Files per database	32767	32767	32767
File size (data)	32 terabytes	32 terabytes	32 terabytes
File size (log)	32 terabytes	32 terabytes	32 terabytes
Foreign key table references per table	253	253	253
Identifier length (in characters)	128	128	128
Instances per computer	16	16	50
Length of a string containing SQL statements (batch size)	65,536 * Network packet size	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server	Up to 2,147,483,647	Limited only by memory	Limited to 60% of memory
Nested stored procedure levels	32	32	32
Nested subqueries	32	32	no limit, at least according to Beta 2 BOL
Nested trigger levels	32	32	32
Nonclustered indexes per table	249	249	249
Objects concurrently open in an instance of SQL Server	2,147,483,647 per database (depending on available memory)	2,147,483,647 per database (depending on available memory)	2,147,483,647 per database (depending on available memory)
Objects in a database	2147483647	2147483647	2147483647
Parameters per stored procedure	2100	2100	2100
Parameters per user-defined function	2100	2100	2100
REFERENCES per table	253	253	253
Rows per table	Limited by available storage	Limited by available storage	Limited by available storage
Tables per database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database
Tables per SELECT statement	256	256	256
Triggers per table	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
Footnotes
1 - By including nonkey columns in the index, you can exceed the 900 byte limit as these columns (used in covering queries) are not computed as part of the 900 byte limit.
2 - I could not find this listed in the SQL Server 2005 Beta 2 BOL.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp" title="'>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp"> 3 - The Maximum Capacity Specifications shows the less or the batch size or 250MB, however Books Online shows 128MB in the entry for
4 - The 16 column limit is for key columns. Additional columns can be included (as in footnote 1) beyond the 15.
5 - Not valid for XML indexes.