Select Middle Record

We can use the SELECT TOP 1 query to find the First or the last record in a table data. From below query we can find the exactly middle record in a table data.

SELECT TOP 1 VchDt
  FROM (SELECT TOP 50 PERCENT VchDt 
          FROM SaleData WITH (NOLOCK)
         ORDER BY VchDt ASC) AS T1 
 ORDER BY 1 DESC

Change tables owner to dbo with sp_changeobjectowner

Sometimes we need to change all tables in the database to be owned by dbo for some maintenance purpose or to fix up some accidental errors. The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It uses sp_changeobjectowner system stored procedure:

DECLARE tabcurs CURSOR

FOR

    SELECT 'SOMEOWNER.' + [name]

      FROM sysobjects

     WHERE xtype = 'u'

OPEN tabcurs

DECLARE @tname NVARCHAR(517)

FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0

BEGIN

    EXEC sp_changeobjectowner @tname, 'dbo'

    FETCH NEXT FROM tabcurs INTO @tname

END

CLOSE tabcurs

DEALLOCATE tabcurs

Function Vs Stored Procedure

Both Function and Stored Procedure (SP) are pre compiled SQL statements but there some basic differences between them: 

• Function must return a value whereas SP may or may not return a value.
• A function can be used in a Join whereas SP can't be used in a Join. 
• Functions can return a table whereas SP can create a table but can't return table.
• XML and output parameters can't be passed to functions whereas it can be with SP. 
• Transaction related statement can be handled in SP whereas it can't be in function.
• We can have extended SP but not extended functions.
• SP can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined SP can be called. Only extended/system defined procedures can be called.
• SP can be called independently using exec keyword whereas function are called using select statements.
• SP can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this

Disconnect all users from a database

We can disconnect all users from a database by just killing all the process connected to the database. Check below stored procedure to do this:


CREATE PROCEDURE dbo.KillAllConnection
@DB_Name SYSNAME
AS

BEGIN

DECLARE @spid INT
DECLARE @strSQL NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID(@DB_Name) AND spid != @@SPID

OPEN processes
FETCH NEXT FROM processes INTO @spid

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'KILLing '+RTRIM(@spid)
SET @strSQL = 'KILL '+RTRIM(@spid)
EXEC(@strSQL)
FETCH NEXT FROM processes INTO @spid

END

CLOSE processes
DEALLOCATE processes

END

GO

Script to retrieve security information

Query to get server level logins and role assigned:
SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
          WHEN b.securityadmin=1 THEN 'securityadmin'
          WHEN b.serveradmin=1 THEN 'serveradmin'
          WHEN b.setupadmin=1 THEN 'setupadmin'
          WHEN b.processadmin=1 THEN 'processadmin'
          WHEN b.diskadmin=1 THEN 'diskadmin'
          WHEN b.dbcreator=1 THEN 'dbcreator'
          WHEN b.bulkadmin=1 THEN 'bulkadmin'
          ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'

Query to get database users and roles assigned:

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Query to get Object level permission for user databases:

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table