sp_MSdbuseraccess

[Jean Davis]

Hi,

Has anyone adapted the sp_MSdbuseraccess SP so that users other than
sysadmins cannot see the system databases when they log in?

Thanks - Jean.

[David Portas]

The following article describes a fix to do just that so as to improve
performance in EM. This isn't a security feature though. You can't stop
users seeing the DBs using other tools.

http://support.microsoft.com/default.aspx/kb/889696

--
David Portas
SQL Server MVP
--

[Jean Davis]

Yes - I've already used that but it still displays the system databases that
you cannot remove the Guest user from eg. master and tempdb.

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:1121338336....@g44g2000cwa.googlegroups.com...

[stah...@juno.com]

AS-IS NO WARRENTY USE AT OWN RISK
Change this line
"select @accessbit = has_dbaccess(@dbname)"
To Below and it seems to work using code at URL
http://support.microsoft.com/d efault.aspx/kb/889696

Tim S

/* Determine whether the current user has access to the
database. */
-- select @accessbit = has_dbaccess(@dbname)
select @accessbit =
CASE
WHEN DB_ID(@dbname) < 5
THEN
(
CASE
WHEN 1 = IS_SRVROLEMEMBER('sysadmin') OR
1 = IS_SRVROLEMEMBER('dbcreator') OR
1 = IS_SRVROLEMEMBER('diskadmin') OR
1 = IS_SRVROLEMEMBER('processadmin') OR
1 = IS_SRVROLEMEMBER('serveradmin') OR
1 = IS_SRVROLEMEMBER('setupadmin') OR
1 = IS_SRVROLEMEMBER('securityadmin')
THEN has_dbaccess(@dbname)
ELSE 0
END
)
ELSE has_dbaccess(@dbname)
END

[Jean Davis]

Thanks Tim - that's done the trick!!

<stah...@juno.com> wrote in message
news:1121389883.6...@g49g2000cwa.googlegroups.com...