March 18, 2006 at 5:54 am
how to hide our database like database system.
i could make my database be readonly, but i wanna hide it.
could anyone help me to realize it???
thanxs before
March 20, 2006 at 7:28 am
What do you mean you want to hide it?
-SQLBill
March 20, 2006 at 9:30 am
You could disable all network protocols but of course then you couldn't access your databases over the network. But it would be hidden!
March 20, 2006 at 3:00 pm
You can't, at least not in SQL Server 2000.
K. Brian Kelley
@kbriankelley
March 25, 2006 at 3:06 pm
Do you mean have your database not show up in EM (Enterprise Manager)?
If so I suggested the following to someone and he said it worked OK to hide the system databases from regular users also hides the databases a user does not have access to.
NO WARRENTY AS-IS USE AT OWN RISK.
See MS Link for code for sp_MSdbuseraccess procedure that hides normal databases a user has no rights to.
http://support.microsoft.com/default.aspx/kb/889696
Modify it like below.
Change this line
"select @accessbit = has_dbaccess(@dbname)"
To Below
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
March 26, 2006 at 11:45 am
This works for EM, but the problem is that the public role has SELECT rights against sysdatabases, meaning they aren't hidden. While SELECT permissions aren't needed for most users, removing them would bring the master database into an unsupported state. Also, you'd have to look at removing permissions from the system stored procedure sp_helpdb... and quickly we see how the problem grows.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply