Navigation

how to find guest account is enabled or disabled sql server

how to find guest account is enabled or disabled sql server ?
 
 
-- How to find Guest account is enabled or disabled 
-- run the below script
CREATE TABLE #tuser (
DBName sysname NULL ,
[UserName] sysname,
hasdbaccess sysname NULL
 
  )

INSERT #tuser
 EXEC sp_MSforeachdb '
 SELECT ''[?]'' AS DBName,name as UserName , hasdbaccess FROM sysusers where name= ''guest''  ;'

select DbName, UserName , case hasdbaccess when '1' then 'Enabled' else 'Disabled' end as Status  from #tuser
DROP TABLE #tuser

-- How to Grant db access
sp_grantdbaccess 'guest'
-- how to revoke dbaccess
sp_revokedbaccess 'guest'
 

-- how to login to sql server using windows and sql server quest account
from the sql server open a new query window
CREATE LOGIN [SQLSERVERNAME\Guest] FROM WINDOWS
-- to enable login for guest account for non-domain authenticated users
You'll also need to map a database user to the guest login (or create a guest user in the database).
[edit: need to make sure your in the right database]
USE [DatabaseName]
GO
CREATE USER [SQLSERVERNAME\Guest] FOR LOGIN [SQLSERVERNAME\Guest]
--
Then grant some rights to do stuff. Highly likely there is an existing Role already in the database to give to the user.
--List all the roles, check the last column, isAppRole, as if there is one, you probably want to use it.
sp_helprole
-- Then add the user to the role and your set
sp_Add_RoleMember 'DatabaseRoleName', [SQLSERVERNAME\Guest]
 
Thanks & Regards

 
 
Comments