–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
Continue readingCategory: SQL Server -DBA
SQL Server -DBA, SQL Server Services, who dropped or altered tables,procedures or databases in SQL Server, What in SQL Server
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
Continue readingHow to get information about BUILTINadministrators in SQL SERVER ?
EXEC master..xp_logininfo @acctname = ‘BuiltinAdministrators’, @option = ‘members’
Continue readingHow 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
Continue readingHow to view all users that have access to SQL Server
SELECT name, type_desc, is_disabled FROM sys.server_principals
Continue readingList 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]
Continue readingWho are having Sysadmin role in SQL SERVER
we can find out the login names having sysadmin rights from below query EXEC sp_helpsrvrolemember ‘sysadmin’
Continue readingList all access permission or provisioned to a SQL user or Windows user/group directly
SELECT [UserType] = CASE princ.[type]
Continue readingSQL 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
Continue readingCreate a SQL Login in SQL Server
USE [master] GO CREATE LOGIN [Pintos] WITH PASSWORD=N’password123′ –password does not meet complexity (use upper ‘P’) MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON,
Continue reading