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

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

User Lync Server Control Panel returns that error "Insufficient access rights to perform the operation"

"Insufficient Access Rights" error when modifying/removing Lync:

1. Open Active Directory Users and Computers

2. In ADUC, make sure "Advanced Features" is turned on in the view menu

3.Open the user account Properties

4.On the Security Tab, Click the Advanced button

5. Enable Inheritance

6. Click OK.
7. Then go to Lync server Control Panel and try to edit or delete.

Should be solved. 

What is SMTP

SMTP

SMTP stands for Simple Mail Transfer Protocol and used to sending email messages between different email servers. SMTP is reliable and efficient way to transfer email. Today mostly email systems used SMTP to transfer their email on the Internet and the POP (Post Office Protocol) or IMAP (Internet Message Access Protocol) protocol is used for retrieved email at client end.

How to Transfer FSMO Roles Windows Server 2003.

This article describes how to transfer Flexible Single Master Operations (FSMO) roles (also known as operations master roles) by using the Active Directory snap-in tools in Microsoft Management Console (MMC) in Windows Server 2003.

FSMO Roles

In a forest, there are at least five FSMO roles that are assigned to one or more domain controllers. The five FSMO roles are:

·         Schema Master: The schema master domain controller controls all updates and modifications to the schema. To update the schema of a forest, you must have access to the schema master. There can be only one schema master in the whole forest.

·         Domain naming master: The domain naming master domain controller controls the addition or removal of domains in the forest. There can be only one domain naming master in the whole forest.

·         Infrastructure Master: The infrastructure is responsible for updating references from objects in its domain to objects in other domains. At any one time, there can be only one domain controller acting as the infrastructure master in each domain.

·         Relative ID (RID) Master: The RID master is responsible for processing RID pool requests from all domain controllers in a particular domain. At any one time, there can be only one domain controller acting as the RID master in the domain.

·         PDC Emulator: The PDC emulator is a domain controller that advertises itself as the primary domain controller (PDC) to workstations, member servers, and domain controllers that are running earlier versions of Windows. For example, if the domain contains computers that are not running Microsoft Windows XP Professional or Microsoft Windows 2000 client software, or if it contains Microsoft Windows NT backup domain controllers, the PDC emulator master acts as a Windows NT PDC. It is also the Domain Master Browser, and it handles password discrepancies. At any one time, there can be only one domain controller acting as the PDC emulator master in each domain in the forest.

You can transfer FSMO roles by using the Ntdsutil.exe command-line utility or by using an MMC snap-in tool. Depending on the FSMO role that you want to transfer, you can use one of the following three MMC snap-in tools:

Active Directory Schema snap-in
Active Directory Domains and Trusts snap-in
Active Directory Users and Computers snap-in

If a computer no longer exists, the role must be seized. To seize a role, use the Ntdsutil.exe utility.

For additional information about how to use the Ntdsutil.exe utility to seize FSMO roles, click the article number below to view the article in the Microsoft Knowledge Base:

(http://support.microsoft.com/kb/255504/ ) Using Ntdsutil.exe to Seize or Transfer the FSMO Roles to a Domain

Transfer the Schema Master Role

Use the Active Directory Schema Master snap-in to transfer the schema master role. Before you can use this snap-in, you must register the Schmmgmt.dll file.
Register Schmmgmt.dll

1.     Click Start, and then click Run.

2.     Type regsvr32 schmmgmt.dll in the Open box, and then click OK.

3.     Click OK when you receive the message that the operation succeeded.

Transfer the Schema Master Role

1.     Click Start, click Run, type mmc in the Open box, and then click OK.

2.     On the File, menu click Add/Remove Snap-in.

3.     Click Add.

4.     Click Active Directory Schema, click Add, click Close, and then click OK.

5.     In the console tree, right-click Active Directory Schema, and then click Change Domain Controller.

6.     Click Specify Name, type the name of the domain controller that will be the new role holder, and then click OK.

7.     In the console tree, right-click Active Directory Schema, and then click Operations Master.

8.     Click Change.

9.     Click OK to confirm that you want to transfer the role, and then click Close.

Transfer the Domain Naming Master Role

1.     Click Start, point to Administrative Tools, and then click Active Directory Domains and Trusts.

2.     Right-click Active Directory Domains and Trusts, and then click Connect to Domain Controller.
NOTE: You must perform this step if you are not on the domain controller to which you want to transfer the role. You do not have to perform this step if you are already connected to the domain controller whose role you want to transfer.

3.     Do one of the following:

o    In the Enter the name of another domain controller box, type the name of the domain controller that will be the new role holder, and then click OK.

-or-

o    In the Or, select an available domain controller list, click the domain controller that will be the new role holder, and then click OK.

4.     In the console tree, right-click Active Directory Domains and Trusts, and then click Operations Master.

5.     Click Change.

6.     Click OK to confirm that you want to transfer the role, and then click Close.

Transfer the RID Master, PDC Emulator, and Infrastructure Master Roles

1.     Click Start, point to Administrative Tools, and then click Active Directory Users and Computers.

2.     Right-click Active Directory Users and Computers, and then click Connect to Domain Controller.

NOTE: You must perform this step if you are not on the domain controller to which you want to transfer the role. You do not have to perform this step if you are already connected to the domain controller whose role you want to transfer.

3.     Do one of the following:

o    In the Enter the name of another domain controller box, type the name of the domain controller that will be the new role holder, and then click OK.

-or-

o    In the Or, select an available domain controller list, click the domain controller that will be the new role holder, and then click OK.

4.     In the console tree, right-click Active Directory Users and Computers, point to All Tasks, and then click Operations Master.

5.     Click the appropriate tab for the role that you want to transfer (RID, PDC, or Infrastructure), and then click Change.

6.     Click OK to confirm that you want to transfer the role, and then click Close.