SQL Server - Permisos sólo para vistas

Saludos.

Existe alguna manera de dar permisos a un usuario sólo para crear y modificar vistas, pero que no pueda crear ni borrar tablas?

Utilizo SQL Server 2008 R2

Gracias.

No existe solo un permiso para crear, modificar SOLO VISTAS, puedes darle los permisos necesario para crear vistas y mediante un trigger de tipo DDL, restringir la modificación o creación de tablas.

Mmm ok. Gracias por responder.

No es del todo correcta la respuesta:

Puedes asignar algo asi:

Realmente este permiso implica un alter ...
en esta tabla puedes ver los permisos disponibles para SQL Server 2008 https://msdn.microsoft.com/en-us/library/ms178569.aspx

Saludos

Checa este ejemplo:

CREATE DATABASE yourdb;
GO

USE yourdb;
GO

CREATE ROLE LimitedCreatorRights;
GO

GRANT CREATE VIEW TO LimitedCreatorRights;
GO

GRANT SELECT ON SCHEMA::dbo TO LimitedCreatorRights;
GO

CREATE USER TestUser WITHOUT LOGIN;
GO

EXEC sp_addrolemember ‘LimitedCreatorRights’, ‘TestUser';
GO

CREATE TABLE dbo.ATest (TestID INT);
GO

One thing we’ve not given is the permission to create tables. In the following examples you will see that I am using the EXECUTE AS and the REVERT commands.

The EXECUTE AS allows you to still be logged in with sysadmin rights, but run these examples using the TestUser permissions and the REVERT returns permissions back to the original user.

So if a user that is a member of this role attempts to create a table in the dbo schema, it’ll fail:

USE yourdb;
GO

— This will fail, as TestUser doesn’t have CREATE TABLE permissions
EXECUTE AS USER = ‘TestUser';
GO

CREATE TABLE dbo.ASecondTable (TestID INT);
GO

REVERT;
GO

Error Msg 262, level 14, state 1, line 2

Create table permission denied in database ‘yourdb’

And, in fact, so will the creation of a view:

— This will fail, as TestUser does have CREATE VIEW rights
— but does not have permission to alter the dbo schema
EXECUTE AS USER = ‘TestUser';
GO

CREATE VIEW dbo.AView AS SELECT TestID FROM dbo.ATest;
GO

REVERT;
GO

Error Msg 2760, level 16, state 1, procedure Aview, line2

The Specified schema name dbo either does not exist or you don’t have permissions

The catch is that the TestUser must have the permission to modify the dbo schema. We can accomplish this by assigning that permission to a role the TestUser is a member of:

— Once permission is granted, re-run the previous CREATE VIEW statement. It will now succeed.

GRANT ALTER ON SCHEMA::dbo TO LimitedCreatorRights;
GO

Now, if you go back and re-run the CREATE TABLE and the CREATE VIEW statements above, you’ll see the CREATE TABLE statement fails (we didn’t give TestUser or any role it is a member of the permission to create a table), but the create view statement will succeed.

Create Table Fails: Error msg create table permission denied in database ‘yourdb’