Technical Article

Find Guest user access level in all databases

,

I used the undocumented sp_MSforeachdb in EXAMPLE:1 to Query all Databases at once in one line code, or you can use a cursor for the same as shown in EXAMPLE:2

There may be other effieient methods and maybe some good use of DMV to same approach, but this is what i use as a Quick Reference, when needed.

I have also included the REVOKE Statement for refrerence, incase you want to remove the Guest access to your Database.

EXAMPLE:3 shows the diff methods to the REVOKE approach.

Just Twisting the Cursor statements a bit, we are able to define a all at onec or one row at a time REVOKE method.

WARNING!!! : TEST this on your TEST- SYSTEM First and use this SCRIPT at your own RISK only.

Study your environment before revoking access to GUEST User.

EXAMPLE: 1
EXEC sp_MSforeachdb 'USE [?];
SELECT ''?'' as dbname,name,hasdbaccess FROM sysusers where name like ''guest'' ;'

EXAMPLE: 2
Declare @holdname sysname
Declare getdbname cursor for
Select name from master.sys.databases order by name
Open getdbname
fetch next from getdbname into @holdname
while @@fetch_status=0
BEGIN
    select @holdname,name,hasdbaccess from sysusers where name like 'guest%'
fetch next from getdbname into @holdname
END
close getdbname
deallocate getdbname

--To remove GUEST Access to the Database:
REVOKE CONNECT FROM GUEST 

EXAMPLE: 3
Declare @holdname sysname
Declare getdbname cursor for
Select name from master.sys.databases order by name
Open getdbname
fetch next from getdbname into @holdname
while @@fetch_status=0
BEGIN

--use this to revoke permissions in all databases at once.
REVOKE CONNECT from GUEST
--    use this to list down all the REVOKE sentences across Databases
select 'USE ' +@holdname+'; REVOKE CONNECT FROM '+name from sysusers where name like 'guest%'

fetch next from getdbname into @holdname
END
close getdbname
deallocate getdbname

--Another approach : 
EXEC sp_MSforeachdb 'USE [?]; REVOKE CONNECT FROM GUEST;'

Rate

4.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4.33 (3)

You rated this post out of 5. Change rating