NJDave (11/19/2012)
HelloI have a request to allow a vendor to have VPN access to my SQL Server that currently has one default instance.
The default instance contains multiple databases for multiple agencies - this creates a security concern.
Is it possible to create a second instance and only allow the vendor access to that second instance through VPN?
no need for additional instances...you can limit access simply by giving the vendor one login, that has a matching user in only a single database.
Is it possible to give a vendor VPN access but restrict their access to anything else but their database?
Thanks in advance
Dave
Remember SQL is deny by default, meaning if you don't explicitly give them access to something , whether by adding them to roles or mistakenly granting sysadmin privileges, they simply cannot access something that you diidn't give them.
I would create a user for your vendor , say VENDORACCESS, and that is the user they can use to access the database in question;
Create LOGIN [VendorAccess] WITH PASSWORD='NotTheRealPassword'
USE [WHATEVER]
Create USER [VendorAccess] FOR LOGIN [VendorAccess]
CREATE ROLE [AlmostOwners]
EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'
EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'
EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'
--can the users EXECUTE procedures? comment out if false
GRANT EXECUTE TO [AlmostOwners]
--allow the users to see view proc and function definitions
Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]
--finally add our user to the role:
EXEC sp_addrolemember N'AlmostOwners', N'VendorAccess'
Lowell