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?
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
- Create a Login on the server with public role.
- Then Create a user on the database that they need to access.
- Grant create table to that user.
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.