I 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
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'
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'
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!