Question about create database,login,user,schema and grant permissions.

Hi,

I created a database,login,user and schema like belows.

---

-- 2. create database
CREATE DATABASE MyTempDatabase;

-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

--
USE MyTempDatabase;

-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;

-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;

---

The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.

How can I grant permissions to the user?(or schema?)

I failed to grant by T-SQL query.

Additionally, what is purppose of the ROLE? Should I create or use it?

I'm confusing in security concept(login,user,schema,role).

Thanks.

i will give u a capsule form of all these database objects

Login : SQL Server uses Two level Security architecture. Server level and Database Level. To access database you should have server level access. But if you have server level access it does not mean that you have database level access. So , Login is nothing but the Server Level Security. User is Database Level Security. If you create a login , you should add that login to a database as Database User, to have access to that database.

Role : is just like Groups in Windows. It is to group users for better admistration so that you can grant permission to the role rather than granting to each user. i think you must use this.

Schema : this is a new concept in sql 2005. This basically groups the objects or it is a container. In earlier versions of sql server , objects were owned by user and both were tightlty coupled. So if you want to drop a user then you need to transfer the objects owned by the user to any other user. In sql server 2005 users are not the owner of the objects but schema is the object owner.

i think Books online is the best resource to uderstand these objects. read and practice

Madhu

|||

To allow the user to create and delete tables make them a member of the ddl_admin role within the MyTempDatabase.

Code Snippet

Use MyTempDatabase

GO

exec sp_addrolemember @.rolename='ddl_admin', @.membername='MyTempLogin'

GO

To allow the user to bulk insert data from a text file they need to be a member of the Buk Insert Fixed Server Role. This is a server wide setting which simply allows them to load data from a text file.

Code Snippet

exec sp_addsrvrolemember @.loginame='MyTempLogin', @.rolename='bulkadmin'

go

Roles are used to make granting permissions to a group of database users easier. Say you have 5 users who all need the same permissions. Instead of setting up there permissions on a user by user basis, you would setup a role and make all the users members of the role. You can then grant the role what ever rights the users need and the users will inheriate those rights from the role. And users can be in more than one role. So if two of your five users need additional rights that the other three don't need you can create a second role, put in the two users and grant that role the extra rights. When this comes into play is when you add a sixth user. You now don't need to remember what rights they need, you simply drop them into the role and they will have the rights.

When using database roles any right which can be granted within a database can be granted to a role.