Database role to create tables

Question

Database role to create tables

jerryhiggins

Old Hand

Points: 362
More actions
December 13, 2010 at 8:27 am

#388427

I need to set up a role that can create tables on an existing database but only want to give read only access to the role for the original tables.
I didn't see anything in the predefined roles that allows the creation of tables.
Any suggestions?
Thanks,
Jerry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Depending on exactly what you want the new role to be able to do you might want to make your new role a member of both the db_ddladmin role and the db_datawriter role (or maybe instead of db_datawriter you would have db_datareader) then deny it all the unwanted permissions on the old tables. Writing a script to generate the deny commands is a better idea than producing them by hand if there are many old tables.

Of course if the new role just has to be able to create tables and do nothing else, this isn't the right approach because you can make your role a member of db_denydatareader and of db_denydatawriter and then grant it the single create database permission. But that would be a strange role to want.

Tom

jerryhiggins Old Hand Points: 362 More actions · Answer 2

I want the new role to be able to read the old tables, but not able to create, edit or delete data.

I will give that a try.

Thanks Tom!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

I would use two roles here. One to read only from old tables, one to allow DDL activities.

If someone can create tables, they will be able to read/write from them since they will own them (or have access through schema). For the "old tables" just grant SELECT access to the role.

jerryhiggins Old Hand Points: 362 More actions · Answer 4

This is not working.

I created a test database with a "teams" table and a "players" table. These are the 2 tables that I don't want users to be able to change.

I created 2 database roles, a view role and a create role.

The view role only has the ability to view the teams and players table. In the view role I have 2 securables, the teams table and the players table. Each of those has grant - select permissions.

In the create role, I have the same 2 securables with select checked, but in this one I have "Deny" selected on everything else, update, delete, etc.

User2 is a member of the create role. So I would think that when I login as User2 I shouldn't be able to delete the team or the players table since I have Deny on delete selected in the create role.

Unfortunately that is not the case.

jerryhiggins Old Hand Points: 362 More actions · Answer 5

I got this working by adding a new schema and a database role. The user can add, delete tables in the new schema and the has privileges in the role to select dbo tables but cannot update, delete, or insert into dbo tables.

honeysmith55 SSC Journeyman Points: 90 More actions · Answer 6

Hi

To create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role is:

CREATE ROLE role_name

[ NOT IDENTIFIED |

IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

For example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role

IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Thanks

web designing company