|
|
| Jean Davis 2005-07-14, 7:23 am |
| 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 2005-07-14, 7:23 am |
| 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 2005-07-14, 11:23 am |
| 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_REPLYI
NG_dportas@acm.org> wrote in message
news:1121338336.729512.71550@g44g2000cwa.googlegroups.com...
> 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
> --
>
| |
| stahta01@juno.com 2005-07-14, 8:23 pm |
| 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('sy
sadmin') OR
1 = IS_SRVROLEMEMBER('db
creator') OR
1 = IS_SRVROLEMEMBER('di
skadmin') OR
1 = IS_SRVROLEMEMBER('pr
ocessadmin') OR
1 = IS_SRVROLEMEMBER('se
rveradmin') OR
1 = IS_SRVROLEMEMBER('se
tupadmin') OR
1 = IS_SRVROLEMEMBER('se
curityadmin')
THEN has_dbaccess(@dbname
)
ELSE 0
END
)
ELSE has_dbaccess(@dbname
)
END
| |
| Jean Davis 2005-07-15, 7:23 am |
| Thanks Tim - that's done the trick!!
<stahta01@juno.com> wrote in message
news:1121389883.622340.106130@g49g2000cwa.googlegroups.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('sy
sadmin') OR
> 1 = IS_SRVROLEMEMBER('db
creator') OR
> 1 = IS_SRVROLEMEMBER('di
skadmin') OR
> 1 = IS_SRVROLEMEMBER('pr
ocessadmin') OR
> 1 = IS_SRVROLEMEMBER('se
rveradmin') OR
> 1 = IS_SRVROLEMEMBER('se
tupadmin') OR
> 1 = IS_SRVROLEMEMBER('se
curityadmin')
> THEN has_dbaccess(@dbname
)
> ELSE 0
> END
> )
> ELSE has_dbaccess(@dbname
)
> END
>
|
|
|
|