Hide all databases USE MASTER GO DENY VIEW ANY DATABASE TO PUBLIC GO
Continue readingAuthor:
How to disable and Enable sa login in SQL Server
–make sure you have a login that is part of the sysadmin role before doing this!!!! USE [master] GO ALTER
Continue readingEnable 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
Continue readingHow 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 reading