Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Database Access: View/Access only databases user/login has access to

Status
Not open for further replies.

Neily

Programmer
Jul 27, 2000
342
GB
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:

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top