Remote computer shutdown or Restart

To restart or shut down a remote computer and document the reason using the command line

Click Start, click All Programs, click Accessories, and click Command Prompt.
ping computer name or IP
if reply
Type shutdown /[r|s] /m \\ComputerName /force
          For Restart:
         shutdown /r /m \\ComputerName /force

1. For Shutdown :
   
   
   shutdown /s /m \\ComputerName /force
   
   ping computer name or IP
   if time out then Okay
    
   

how to restart Sql service using Batch File

1.Open a new text file from notepad. type the following commands in the text file
2.Save the file as RestartSql.bat on to your desktop
3.whenever you want to restart your Sql Service...just double click the RestartSql.bat on ur desktop

********************************************************************************

net stop "SQL Server Agent (MSSQLSERVER)"
net stop MSSQLSERVER
net start MSSQLSERVER
net start "SQL Server Agent (MSSQLSERVER)"

*******************************************************************************

How to Restart IIS Using Batch file

1.Open a new text file from notepad. type the following commands in the text file
2.Save the file as RestartIIS.bat on to your desktop
3.whenever you want to restart your IIS...just double click the RestartIIS.bat on ur desktop

*******************************************************************************

net stop w3svc
net stop iisadmin
net start iisadmin
net start w3svc

*******************************************************************************

How to Server role and Database Role from SQl server

Run the script under Master Database

**************************************************************************
select
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on sp.sid=dbp.sid join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id

*********************************************************************



Exciting 6 Android Marshmallow Features

How to get user roles from SQl Server

*****************************************
SELECT p.NAME,m.NAME
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
********************************************

SQL Server: How to get login IDs' Server Roles

Returns the names and id's of the roles and their members

SELECT sys.server_role_members.role_principal_id, role.name AS RoleName,   
    sys.server_role_members.member_principal_id, member.name AS MemberName  
FROM sys.server_role_members  
JOIN sys.server_principals AS role  
    ON sys.server_role_members.role_principal_id = role.principal_id  
JOIN sys.server_principals AS member  
    ON sys.server_role_members.member_principal_id = member.principal_id; 

Fixed server-level role

	Description
sysadmin	Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin	Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin	Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins. ** Security Note *\* The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin	Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin	Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadmin	Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin	The diskadmin fixed server role is used for managing disk files.
dbcreator	Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public	Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public. Note: public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.

How to block IPs to connect to Database Server

After Running this query one trigger and one table will be created.
Trigger Name : block_ipaddress
Table Name: IPBLock
The add ip addresses which are needed to block.


********************************************************************************

USE [master]
GO
/****** Object:  Table [dbo].[IPBLock]    Script Date: 04/20/2017 13:43:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IPBLock](
    [ipaddress] [varchar](15) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

/****** Object:  DdlTrigger [block_ipaddress]    Script Date: 04/19/2017 20:24:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [block_ipaddress]
ON ALL SERVER
FOR LOGON
AS
BEGIN
            DECLARE @capturedip NVARCHAR(15);
            SET @capturedip = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
            IF EXISTS(SELECT ipaddress FROM master.dbo.IPBLock WHERE ipaddress = @capturedip)
            BEGIN
                        Print 'Your IP Address is blocked, Contact Administrator'
                        ROLLBACK
            END
            ELSE
            BEGIN
                        DECLARE @IPRange VARCHAR(15)
                        SELECT @IPRange= SUBSTRING(@capturedip,1,LEN(@capturedip)-CHARINDEX('.',REVERSE(@capturedip)))+'.*'
                        IF EXISTS(SELECT ipaddress FROM master.dbo.IPBLock WHERE ipaddress = @IPRange)
                        BEGIN
                            Print 'Your IP Address Range is blocked, Contact Administrator'
                            ROLLBACK
                        END
            END
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [block_ipaddress] ON ALL SERVER
GO

*********************************************************************************