Solved

guest User Access

Posted on 2014-03-18
3
362 Views
Last Modified: 2014-03-19
Hi,

Is there a query I can run to determine which permissions the guest account has? Would it be considered an exception if the guest has access to master, msdb, and tempdb?

Thanks!

--
Vic
0
Comment
Question by:VicBel
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 39938021
"You should not disable the guest user in the msdb database in SQL Server'

http://support.microsoft.com/kb/2539091

"how to find guest account is enabled or disabled sql server"
https://sites.google.com/site/jayantdass/how-to-find-guest-account-is-enabled-or-disabled-sql-server
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39938225
>> is there a query ... <<

Takes more of a mini-script, like below, to make sure you include all permissions that might have been given to the guest account:

USE msdb

DECLARE @guest_principal_id int
SELECT @guest_principal_id = principal_id
FROM sys.database_principals
WHERE
    name = 'guest'

SELECT *
FROM sys.database_permissions dp
WHERE
    dp.grantee_principal_id = @guest_principal_id

SELECT dp.name
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON
    dp.principal_id = drm.role_principal_id
WHERE
    drm.member_principal_id = @guest_principal_id


>> Would it be considered an exception if the guest has access to master, msdb, and tempdb? <<

No, and you don't want to remove "CONNECT" permission from guest to any of those databases (unless, I guess, you're absolutely certain you know what you're doing and that it won't cause an issue, but only a very experienced DBA should even try it).
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39940499
Hope this helped you out here.  Good luck on future qs.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question