0

We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want them to see our current database inside. I mean like tables, stored procedures etc.. How to grant them just to create a table but not to see other structures of our database?

1

This should do the work. I tested it in my test environment, works for me.

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TutorialDB]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
GRANT CREATE TABLE TO [TestLogin];
GO
  1. Create a Login on the server with public role.
  2. Then Create a user on the database that they need to access.
  3. Grant create table to that user.
0

Firstly, if they are installing their application then surely it would require their own database and not access to the other databases on this server?

However, granting their login member of the dbcreator server role allows them to CREATE new databases on the server.

If you need to allow them to create a table in an existing database then I'd suggest that you create a new schema for them to use and only allow their user in that database to have ALTER permission on that schema. That way they have no access at all to your existing tables.

  • It's worth noting that dbcreator will also give them the ability to DROP, ALTER and RESTORE over ANY database on the server - not good. Why not just grant CREATE TABLE? – George.Palacios Jul 25 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.