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;'

Read 611 times
(2 in last 30 days)

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