SQL Server – Set Up Read-Only Privileges on dbo Schema and Allow Entity Creation in Another Schema

permissionsschemasql serversql-server-2008-r2

Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for the custom schema?

Best Answer

You shouldn't think about permissions in the sense that you have to own something to make changes to it. All you need to create an object in a schema is the ability to create an object in the database, and authorization on the schema (there are other ways but they are less secure). You also should think about applying the same permissions to n number of users via a role as opposed to applying those permissions individually to each user.

Here's a working example that demonstrates the syntax required for all of the things you want to do, I believe.

First, create logins from Windows, if they don't already exist:

USE [master];
GO
CREATE LOGIN [Domain\Username1] FROM WINDOWS;
CREATE LOGIN [Domain\Username2] FROM WINDOWS;

Add those users to the database (create this database as a test, perhaps):

USE a_database;
GO
CREATE USER [Domain\Username1] FROM LOGIN [Domain\Username1];
CREATE USER [Domain\Username2] FROM LOGIN [Domain\Username2];

Create a schema for them:

CREATE SCHEMA Pyramid;

Create a role, so that you don't have to maintain these permissions on every individual user, and add both users to that role:

CREATE ROLE PyramidRole;
GO
-- on modern versions:
ALTER ROLE PyramidRole ADD MEMBER [Domain\Username1];
ALTER ROLE PyramidRole ADD MEMBER [Domain\Username2];
-- on dinosaurs:
EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username1]'; 
EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username2]';

Set the permissions on the role:

-- need specific create / alter permissions:
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE
  TO PyramidRole;

-- to be explicit, you want them to /read/ from dbo, so:
GRANT SELECT, VIEW DEFINITION ON SCHEMA::dbo TO PyramidRole;

-- finally, this will allow them to create/alter objects in their schema:
ALTER AUTHORIZATION ON SCHEMA::Pyramid TO PyramidRole;
-- you may need other permissions depending on what else you need them to do

Now, try it out:

EXECUTE AS USER = N'[Domain\Username1]';
GO
CREATE TABLE dbo.what(id int);     -- Fails with Msg 2760
GO
CREATE TABLE Pyramid.what(id int); -- Succeeds
GO
REVERT;

Now, as you add more users and you want them to inherit this same set of permissions, you just add them to the database, and then add them to the role using ALTER ROLE ... ADD MEMBER ....

More info and more syntax examples:

Related Solutions

Sql-server – SQL Server 2008: How to change the default schema of the dbo

Each database is owned by a server principal (aka login). Inside that database, the owning principal is known as dbo (aka *D*ata*B*ase *O*wner). The database principal (aka user) loses its real name.

For example, for a database I own:

select  [user].name as UserName -- Database specific
,       [login].name as LoginName -- Server wide
from    sys.databases d
join    sys.database_principals as [user]
on      [user].sid = d.owner_sid
join    sys.server_principals as [login]
on      [login].sid = d.owner_sid

Will print "dbo", "Andomar". If you'd change the owner to sa:

exec sp_changedbowner 'sa'

The query would return "dbo", "sa".

You cannot modify the default schema for the user that owns a database. It is always user name dbo with default schema name dbo.

Sql-server – Can view adapt to implicitly reference user’s default schema

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s) 
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x 
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.

Best Answer

Related Solutions

Sql-server – SQL Server 2008: How to change the default schema of the dbo

Sql-server – Can view adapt to implicitly reference user’s default schema

Related Question