2

I've created a simple database, a table, a schema, and a table with that schema ( so, 2 tables ).

--sysadmin
create database dba_schema
go

use dba_schema 
go

create table tabela1 ( id int ) 
go

--LOGIN---------------------------------------------  
USE [master]
GO
CREATE LOGIN [schema_user] WITH PASSWORD=N'schema_user', 
DEFAULT_DATABASE=[dba_schema], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [dba_schema]
GO
CREATE USER [schema_user] FOR LOGIN [schema_user]
GO
USE [dba_schema]
GO
ALTER USER [schema_user] WITH DEFAULT_SCHEMA=[dbo]
GO

( Manually I gave read, write and DLL permissions )
------------------------------------------------

create schema schema1
go

create table schema1.tabela1 ( id int ) 
go

Then I connected with the user schema_user ( that has now only read, write, and ddl permission ).

But, even not giving permission to that schema, I could select both tables ( dbo schema, and the schema I've created )

select * from dbo.tabela1

select * from schema1.tabela1

Is this supposed to happen? I thought that, when we create a schema, we should GRANT permission to that schema, for a user to select from it, but as I see, after creating a schema, we should DENY permission on it, for user not to select. Is this approach right? Its default schema is DBO.

  • Do you have any custom server-level roles that may be granting these permissions? – John Eisbrener Jul 31 '17 at 14:46
  • No No , It's just a test environment I have, with nothing special. I created the database and tables with the sysadmin user, but i'm using the schema_user to select. schema_user has nothing special. – Racer SQL Jul 31 '17 at 15:20
  • Maybe...DB_DATAREADER gives permissions for ALL tables ( including all schemas ) ?? – Racer SQL Jul 31 '17 at 15:22
  • 2
    Yes of course. Unless you deny SELECT on some object explicitly, members of db_datareader role have SELECT on database – sepupic Jul 31 '17 at 15:26
  • Oh...case closed. – Racer SQL Jul 31 '17 at 15:28
2

I would confirm that you are connecting using the correct login, and that you have not incorrectly assigned some other permission.

I executed the script that you provided above, and then used execute as to test the select permissions and received SELECT permission denied errors for both (as I would expect).

Adjusted comments slightly, but the following I received what I expected (SQL Server 2012)

/* Create new database */
CREATE database dba_schema
GO

use dba_schema 
GO

/* Create new table in dbo schema */
CREATE TABLE dbo.tabela1 ( id int ) 
GO

/* Add new login */  
USE [master]
GO
CREATE LOGIN [schema_user] WITH PASSWORD=N'schema_user', 
DEFAULT_DATABASE=[dba_schema], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

/* Add new user */
USE [dba_schema]
GO
CREATE USER [schema_user] FOR LOGIN [schema_user]
GO

/* Set default schema for user */
USE [dba_schema]
GO
ALTER USER [schema_user] WITH DEFAULT_SCHEMA=[dbo]
GO

/* Create new schema */
CREATE SCHEMA schema1
GO

/* Add new table to new schema */
CREATE TABLE schema1.tabela1 ( id int ) 
GO

/* Impersonate the new login of schema_user and query the tables */
EXECUTE AS LOGIN = 'schema_user';
/* Confirm we've impersonated correctly */
SELECT SUSER_NAME();
/* Run the selects */
SELECT * FROM dbo.tabela1;
SELECT * FROM schema1.tabela1;
REVERT;
  • Hey Nic. Yes, I'm using the right user for this test ( schema_user ) . I gave read, write and dll permissions on this user. yes without this, I receive permission errors. – Racer SQL Jul 31 '17 at 14:39

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.