CODEWITHSUNDEEP
sql-serverazure-sql-database
alex
alex

Reputation: 71

SQL Server user permission on different schema

I have an SQL Azure database and there are already thousands of objects under the DBO schema.

I want to create a new user with specific access requirements

the requirement is below.

  1. User can only see a few of the objects under the dbo schema
  2. User can make an update/select/alter/execute..etc. any type of modifications, including adding or removing columns from the objects they are allowed to see.
  3. User can add new objects under the dbo schema.
  4. User should not see the objects(which they are not allowed to see) under the dbo schema in the SSMS browser.

Can you help me to setup the user with specification above?

Upvotes: 0

Views: 2124

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6043

As Dan Guzman said this would be possible without #3. Beacuse "Create table" is only a database level permission, cannot be assigned at the schema level.
A user can be defined as the schema owner. If the user has "Create Table" permissions at the database level and is a schema owner, all tables will be created in the schema he/she owns.

  1. Create login and user in Azure SQL:
use master;
CREATE login user1 with password='SafePassword'

use userDb;
CREATE USER user1 FOR LOGIN user1;  

GO
  1. We can use following query to generate grant T-SQL script. Select the tables you want to grant to the user.
SELECT 'GRANT SELECT,INSERT,DELETE,UPDATE ON "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO "user1"' FROM information_schema.tables

Upvotes: 1

Related Questions