Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 2, 2014 11:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 3, 2016 1:32 PM
Points: 449, Visits: 1,077
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 2, 2014 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 14,326, Visits: 37,418
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

--
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!
Post #1527254
Posted Thursday, January 2, 2014 1:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 3, 2016 1:32 PM
Points: 449, Visits: 1,077
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