Revoke access for all Orphaned users in a server

When a database is moved or restored from one server to another server the login Id's stored in the master database do not align with the login Id's stored in the user database, which account for the orphaned users in the databases.


To get the list of Orphaned users in a database, execute the below query on the particular database.
Exec sp_change_users_login 'Report'


To cycle through all the databases in a server to find the orphaned users, execute the below query

Exec sp_MSforeachdb 'use ?;
Select db_name() as DatabaseName
Exec sp_change_users_login ''Report'''

To Revoke access for all the Orphaned Users in a server, Run the below script

Create a History table in tempdb or in any other DB as required or if required.


Create Table tempdb..OrphUsers_Histroy
(Username nvarchar(250),
userSID varbinary (85),
SQLText nvarchar(500),
Fixedon datetime)


Select * from tempdb..OrphUsers_Histroy

Exec sp_MSforeachdb 'use ?;


Create Table tempdb..OrphUsers 
(Username nvarchar(250),
userSID varbinary (85),
SQLText nvarchar(500))


insert into tempdb..OrphUsers (Username,userSID) 
Exec sp_change_users_login ''Report''


update tempdb..OrphUsers set SQLTEXT=''Exec sp_revokedbaccess ''+username+''''


while (select COUNT(*) from tempdb..OrphUsers )<>0
Begin
declare @SQLTEXT nvarchar(500)
select @SQLTEXT=SQLText from tempdb..OrphUsers
exec sp_executesql @SQLTEXT


/* Remove this insert statement if you do not want to log history */ 
insert into tempdb..OrphUsers_Histroy
select Username,userSID,SQLText,getdate() from tempdb..OrphUsers where SQLTEXT=@SQLTEXT


delete from tempdb..OrphUsers where SQLTEXT=@SQLTEXT
END


drop table tempdb..OrphUsers '

Select * from tempdb..OrphUsers_Histroy

Query to find Dependant Objects

Select DISTINCT (OBJECT_SCHEMA_NAME(id)+'.'+ OBJECT_NAME(id)) AS [Object Name] ,(OBJECT_SCHEMA_NAME(depid)+'.'+ OBJECT_NAME(depid)) AS [Is Dependant on], OBJECTPROPERTYEX(id,'BaseType') AS [Object Type],OBJECTPROPERTYEX(depid,'BaseType') AS [Object Type of Is Dependant on] From sys.sysdepends Where OBJECTPROPERTYEX(id,'IsMSShipped')=0 -- To get only user created object

List of SQL Server instances currently installed in your network

To get a list of SQL Server instances currently installed in your network, execute either of the below commands

Exec xp_cmdshell 'SQLCMD /L'
OR
Exec xp_cmdshell 'OSQL /L'


If you want to populate a text file with the results then, open the command prompt (Go to Run, Type cmd and click ok) and type this command and hit enter
SQLCMD /L > C:\ListOfServers.txt
This command will populate the text file ListOfServers.txt with the results obtained from SQLCMD /L

Identity Column

We all know about setting Identity to a column of a table.
By setting identity to a column, we mean that the column should be populated automatically by incrementing numbers based on the seed and increment option we give.
But all these days, i was setting the identity to a column of a table which had more than 2 columns.
Just think of having a table with only one column and setting identity to that Column.

Create Table IdentityTest
(Slno int identity(1,1))

When you run this query, it will create a table named IdentityTest with the column Slno with identity on it.
What next????
The next question would be, how to insert data to this table?
Any idea, on how to insert data to this table?
Here is how you insert data to this table..

Insert Into IdentityTest Default Values

And now after reading this post, you are now familiar with inserting data to a table which has only one column and identity set on it.  :)

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

1. Maintenance

2. Miscellaneous

3. Informational

4. Validation

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Some of the Maintenance DBCC Statements are:

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC SHRINKFILE

DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:

DBCC dllname (FREE)

DBCC HELP

DBCC FREESESSIONCACHE 

DBCC TRACEOFF

DBCC FREESYSTEMCACHE

DBCC TRACEON

Some of the Informational DBCC Statements are:

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

Some of the Validation DBCC Statements are:

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS  

DBCC CHECKTABLE

DBCC CHECKDB

Some of the useful DBCC commands along with their syntax and examples are listed down and are available for download. Click here to download your copy now.
Reference: BOL