I have to create a new role which should have "Select" permission on all tables created by other users and
2.should have ability to create tables and have access to perform DDL/DML on the tables created by that user.
This should be on sql 2008
are you using multiple schemas, or just a single default schema(dbo)?
DDL? so they can drop tables and columns?
DML, so they can insert/update/delete?
sounds like you really want something that's almost a database owner, but not quite?
CREATE ROLE [AlmostOwners]
EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'
EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'
EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'
--can the users EXECUTE procedures? uncomment if true
GRANT EXECUTE TO [AlmostOwners]
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!