T-SQL Tips

T-SQL Tip: Retrieving Security-Related Info for SQL Server Logins

—— Retrieves Security-Related Information— for all the SQL Server Logins—— SQL Server versions  supported: SQL Server 2005 or later—SELECT [name] as LoginName,LOGINPROPERTY ([name] , ‘DefaultDatabase’) as DefaultDatabase,LOGINPROPERTY ([name] , ‘DaysUntilExpiration’) as DaysUntilExpiration,(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsExpired’),0) WHEN 0 THEN ‘False’ ELSE…

T-SQL Tip: Getting the File Locations for all DBs in a SQL Server Instance

—— Dynamically builds  T-SQL statements for retrieving the file — locations for all the databases in the SQL Server Instance—— SQL Server versions  supported: SQL Server 2005 or later—SELECT ‘use ‘+ [name]+’; select ”’+[name]+”’ as DBName,cast ([name] as varchar(45)) as LogicalFileName,cast (filename…

T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances

—–The Entire Operation Takes Place on the Local Instance— USE [master]GO–Create the linked server objectEXEC master.dbo.sp_addlinkedserver @server = N'[LinkedServerName]’, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N'[DestSQLInstanceName]’ –Set up the user mapping between local and remote instancesEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'[LinkedServerName]’,@useself=N’False’,@locallogin=N'[LocalLogin_SQL_or_Windows]’,@rmtuser=N'[Remote_SQL_Login_Name]’,@rmtpassword='[Remote_SQL_Login_Password]’GO –Example of querying a remote table select * from [LinkedServerName].[Database_Name].[Schema_Name].[Table_Name] For more…