CODEWITHSUNDEEP
sqlsql-server-2008securitypermissionsroles
DanBennett
DanBennett

Reputation: 448

User/Role can Create table or view, but not select from it SQL Server 2008 - Ownership not passing to creator

I'm trying to create a role to give a few users permission to create and alter views, procedures and tables. I don't want these users to be able to select from/update/delete/alter etc. any table in the database, there are tables we want to keep control of - but they should have full permissions on any objects they create.

I've given the users permissions to create views etc. and that works fine, but they can't then select from views they then create. Is it possible to do this?

-- ADDED 25/july/2013

Example: An example user Mike has specific permissions granted on a handful of tables. All Grant, no Deny. No other database level permissions beyond "connect" Plus is a member of public (not altered - no denys), plus 3 other roles we have set up

Role: Standard_Reader Specific Select permissions on a number of tables. All Grant, no Deny. No other database level permissions

Role: SensitiveDemographicsReader Specific Select permissions on sensitive tables. All Grant, no Deny

Role: Analyst No Specific securables Database level permissions: Create Function Create Procedure Create Table Create View

This user can create a table or view, but once created, can't select from it.

Is it possible to set up SQL server so that whenever a user user creates a table or view they then have permissions to select from it (assuming they have permissions on underlying tables in view)

-- EDIT

After some investigation it has become apparent that for some reason in our database, ownership of objects is not acruing to their creators.

Found using this code

select so.name, su.name, so.crdate from sysobjects so join sysusers su on so.uid = su.uid
order by so.crdate

All owners, with a couple of exceptions are DBO.

I can't understand why ownership is not passing to the creators of objects. Any idea what could cause this?

Upvotes: 0

Views: 2531

Answers (3)

Coding Duchess
Coding Duchess

Reputation: 6919

you need to grant SELECT on the schema to user/group:

GRANT SELECT ON SCHEMA::dbo TO User/Group;

Upvotes: 0

Malkin
Malkin

Reputation: 159

Sounds like what you're using to deny them in the first place is overriding the default settings. Can you post more information on what permissions the users have?

Can't comment :(

Upvotes: 1

sdweldon
sdweldon

Reputation: 189

I would comment but lack privileges; have you taken a look at MySQL table permissions? It's a rather good system.

Upvotes: 0

Related Questions