Firstly, I'm using SQL Server 2008 Express on Windows Server 2003.
A number of small projects are being hosted on this server. Each project will be given a non-admin local account to connect to the machine and also a SQL account (same name and password on both).
When they login to SQL server (either remotely or locally via RDP) I want them to only see the database they have access to.
I am also trying to create a script that will set this up correctly as I may need to deploy the server a few times, and I'd like it to be consistent each time. But anyway...
I currently create the user and login using:
Although this give me access to the Test database (and not access all other databases), I don't see it in the Object Explorer window.
Is this possible, or must I grant "View Any Database" but then they just won't have access?
Sees strange if this can't be done!
Thanks in advance!
Neil
A number of small projects are being hosted on this server. Each project will be given a non-admin local account to connect to the machine and also a SQL account (same name and password on both).
When they login to SQL server (either remotely or locally via RDP) I want them to only see the database they have access to.
I am also trying to create a script that will set this up correctly as I may need to deploy the server a few times, and I'd like it to be consistent each time. But anyway...
I currently create the user and login using:
Code:
USE master
GO
CREATE DATABASE Test
GO
USE Test
GO
CREATE LOGIN Test WITH PASSWORD = 'L1v35c0r3', DEFAULT_DATABASE = [Test]
GO
CREATE USER Test FOR LOGIN Test;
GO
EXEC sp_addrolemember 'db_owner', 'Test'
GO
USE master
GO
DENY VIEW ANY DATABASE TO Test;
GO
EXEC sp_addrolemember 'db_datareader', 'Test'
Although this give me access to the Test database (and not access all other databases), I don't see it in the Object Explorer window.
Is this possible, or must I grant "View Any Database" but then they just won't have access?
Sees strange if this can't be done!
Thanks in advance!
Neil