Roles ans Users !!

[karephul]

Database is ARTICLES
I made this role using user "sa"

CREATE ROLE sysuser;
GRANT insert, update, delete, select ON PUBLICARTICLES TO sysuser;
GRANT CREATE TABLE TO sysuser with GRANT OPTION;

" Then I created a user in the same login"

CREATE LOGIN artuser
WITH PASSWORD = 'artuser53';
USE ARTICLES;
CREATE USER artuser FOR LOGIN artuser;

sp_addrolemember @rolename = 'sysuser',
@membername = 'artuser';

----------

Now when I login using user "artuser"
I am unable to create a table and the error is ..
Cannot create table in "dbo" schema !!

but according to me, it should be able to create table and shold be able to GRANT permission on that created table !!

what could be the problem !!

 

[mrdenny]

Not quite. SQL 2005 is funky with these kinds of permissions.

You need to grant the create table as it applies to a schema.

GRANT CREATE TABLE ON SCHEMA::dbo to sysuser

This will allow the user to create tables.

The WITH GRANT option will allow the members of the sysuser role to grant the CREATE TABLE privilage no the ability to view the tables. In order to be able to grant permissions on these tables you need to grant the CONTROL permission to the schema.

GRANT CONTROL on SCHEMA::dbo to sysuser.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com