SQL Server -DBA Archives - Page 14 of 17 - NTSEQ:- A IT Infrastructure and Cyber Security Blogs

Enable Windows Authentication & Mixed Mode Authentication in SQL SERVER

–Enable Windows Authentication (requires a restart of services) USE [master] GO EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SoftwareMicrosoftMSSQLServerMSSQLServer’, N’LoginMode’, REG_DWORD, 1 GO –Enable

How to Drop the BUILTINadministrators in SQL SERVER?

–Drop the BUILTINadministrators USE MASTER IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N’BUILTINAdministrators’) DROP LOGIN [BUILTINAdministrators] GO –Verfiy

How to get information about BUILTINadministrators in SQL SERVER ?

EXEC master..xp_logininfo @acctname = ‘BuiltinAdministrators’, @option = ‘members’

How to Get information of all SQL Logins and Windows users in SQL SERVER

SELECT name AS Login_Name,TYPE, type_desc AS Account_Type FROM sys.server_principals WHERE TYPE IN (‘U’, ‘S’, ‘G’) ORDER BY name, type_desc

How to view all users that have access to SQL Server

SELECT name, type_desc, is_disabled FROM sys.server_principals

List all access to a sql user or windows user/group in SQL SERVER

–List all access to a sql user or windows user/group directly SELECT [UserName] = CASE princ.[type] WHEN ‘S’ THEN princ.[name]

Who are having Sysadmin role in SQL SERVER

we can find out the login names having sysadmin rights from below query EXEC sp_helpsrvrolemember ‘sysadmin’

SQL Server Roles & Database Level Role in SQL Server?

What are SQL Roles ? Predefined collection of objects and permissions? Roles allow the dba to manage permissions more