Granting table level permissions in sql express 2005

Question

I created a login to connect to SQL SERVER.

create login bobLogin with password = 'bobpass' , default_database = bobDB

but when i am connecting sql server using this, it does not connects? because it needs a user.

so i created a user:

create user bobDB_USER for login bobLogin

then i connected to sql server using bobLogin & tried to create table:

create table bobDbTable(eid int)

which gives permission denied error;

so i granted permission:

GRANT CREATE TABLE TO bobDB_USER

then i again connected using bobLogin, & tried to create a table but it gave error:

The specified schema name "dbo" either does not exist or you do not have permission to use it.

why so? its creating the table in the dbo schema, thats why? so how do i grant him this permission ?

i dont want to create a new schema. is it necessary?

score 1 · Accepted Answer · answered Mar 01 '11 at 06:43

1

You would need to GRANT ALTER ON SCHEMA::dbo TO bobDB_USER to allow objects to be created in the dbo schema.

I would also use a Role too.

create role bobDB_ROLE
EXEC sp_addrolemember 'bobDB_ROLE', 'bobDB_USER'
GRANT ALTER ON SCHEMA::dbo TO bobDB_ROLE

However, you could addbobDB_USER into db_owner if it requires these rights

EXEC sp_addrolemember 'db_owner', 'bobDB_USER'

Note: end user permissions are quite different to admin type rights. If 'bobDB_USER' is an end user, then they should not be creating objects

answered Mar 01 '11 at 06:43

gbn

@gbn, sir i have a question on : http://stackoverflow.com/questions/5151363/creating-multiple-users-for-a-c-net-winform-application-using-sql-server-express – sqlchild Mar 01 '11 at 06:55
@gbn, sir, when i do this : use bobDB, create login bobLogin with password = 'bobpass' , default_database = bobDB , use bobDB , create user bobDBUser for Login bobLogin , then is bobDBUser not the owner of bobDB by default because i created him after the use bobDB statement, then who is he? – sqlchild Mar 01 '11 at 06:58
Just an end user. Server-level owner is recorded in sys.databases. Database level owner has to be GRANTed – gbn Mar 01 '11 at 07:02
@gbn, is creating tables inside dbo schema good? or shall i create a new schema named bobSchema? whats the use of dbo schema – sqlchild Mar 01 '11 at 07:04
I actually use a schema "Data". It is owned by dbo though. See my question here which may help http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas – gbn Mar 01 '11 at 07:08
@gbn , can a schema be owned by other schema? – sqlchild Mar 01 '11 at 07:17
No, a schema is a "securable", not a "principal". A principal owns (or has permissions etc) on securables. A securable can not own a securable. – gbn Mar 01 '11 at 07:30

1 Answers1