December 2007 | SansSQL

Wednesday, December 26, 2007

What's New in SQL Server 2005

SQL Server Login Transfer Scripts

--The following code script out the sp_addlogin calls for the SQL Server logins:


SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''' ,', @defdb = ''' + dbname + '''' ,', @deflanguage = ''' + language + '''' ,', @encryptopt = ''skip_encryption''' ,', @passwd =' , cast(password AS varbinary(256)) ,', @sid =' , sid FROM syslogins WHERE name NOT IN ('sa') AND isntname = 0

Each row in the column list is a column in the result. The script uses the isntname column to ascertain whether a login is a SQL Server login or an NT login. Executing the above code in a Query Analyzer grid shows that the binary data (and other parameters) are in separate columns. Also, because the password column is in Unicode (and encrypted), the code converts the password column into VARBINARY (256), so that you don't lose characters.

--To script the NT logins:

SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + '''' ,' EXEC sp_defaultdb @loginame = ''' + loginname + '''' ,', @defdb = ''' + dbname + '''' FROM syslogins WHERE loginname NOT IN ('BUILTIN\Administrators') AND isntname = 1

Save the output as a file and execute that file in the destination server. You would get an error message if a login already exists. If you don't want to get the error messages, script with IF NOT EXISTS and a check against the login name column in the destination server for each call to sp_addlogin and sp_grantlogin.

Tuesday, December 25, 2007

Column Search

/*This script searches the specified column name from the database and displays the table names in which it is present.*/

select, * from syscolumns, sysobjects where like '%EmpID%'and = and (sysobjects.xtype='U' or sysobjects.xtype='S')

Database files info

This script returns the main properties for all data files and log files in the current
database including logical and physical file names, size, used space, and file growth


use dbname

select fileid, sf.groupid, grp=left([groupname],20), Logicalname=left([name],20),
size_mb=[size]/128 ,used_mb=FILEPROPERTY([name], 'SpaceUsed')/128,file_growth=case when (sf.status&0x100000) > 0 then str(growth)+' %' else str(growth/128)+' mb' end,max_mb=case when [maxsize]<0 then 'Unrestricted' else str([maxsize]/128) end,PhysicalName=left(filename,70)from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupidorder by 1