Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create new role Expand / Collapse
Author
Message
Posted Thursday, January 02, 2014 11:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:14 AM
Points: 374, Visits: 911
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
Post #1527242
Posted Thursday, January 02, 2014 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 12,744, Visits: 31,068
balasach82 (1/2/2014)
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?

USE [YourDatabaseName]
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]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1527254
Posted Thursday, January 02, 2014 1:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:14 AM
Points: 374, Visits: 911
Thanks Lowell. I followed your steps. Will have to wait and see if there is an issue.

Thanks
Post #1527264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse