views:

51

answers:

1

if i do:

CREATE SCHEMA [test] AUTHORIZATION [testuser]

testuser doesn't seem to have any permissions on the schema, is this correct? I thought as the principal that owns the schema, you had full control over it?

What permission do i need to grant testuser so that it has full control over the test schema only?

Edit: by "full control" i mean the ability to CRUD tables, views, sprocs etc

Edit: here is my full code:

CREATE DATABASE [testdb]
Go
USE [testdb]

CREATE LOGIN [andrewbdesktop\testuser] FROM WINDOWS
Go

CREATE USER [andrewbdesktop\testuser] FROM LOGIN [andrewbdesktop\testuser]
Go

CREATE SCHEMA [test]
    AUTHORIZATION [andrewbdesktop\testuser]
Go

ALTER USER [andrewbdesktop\testuser] WITH DEFAULT_SCHEMA = [test]

Thanks

+1  A: 

I believe that with your statement, you're making that user the owner of the schema, so it should already have full control over the objects in that schema by default.

If you want more fine-grained control you can do statements like:

GRANT EXECUTE ON SCHEMA::test TO testuser
GRANT INSERT ON SCHEMA::test TO testuser
GRANT SELECT ON SCHEMA::test TO testuser
GRANT UPDATE ON SCHEMA::test TO testuser
GRANT DELETE ON SCHEMA::test TO testuser
Dave Markle
Thats what i thought, if i do `create table [test].[newtable] (id int)` i get: `CREATE TABLE permission denied in database 'test'`
Andrew Bullock
You also have to be a valid user in the database, and you have to explicitly not be denied access to create a table in that schema.
Dave Markle
hmm ive added all my code to the question, does this look like it would be causing me access denied? Thanks
Andrew Bullock
No, your code looks good to me. I even tested it out on my box. Can you post your exact error message?
Dave Markle
i run that code as a dbo, then log onto sqlserver using windows authentication as testuser, if i try and create a table i get: `CREATE TABLE permission denied in database 'testdb'.`
Andrew Bullock