Best Role to to create, update and read tables.

Question

Post reply

Best Role to to create, update and read tables.

SQListic

SSCertifiable

Points: 6904
More actions
August 9, 2012 at 9:24 am

#392924

wht is the Best Role to to create, update and read tables. Do i need to create a new role? if so let me know what all i need to do ?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think it's a best practice to create your own role that encapsulates just the required permissions.

Then i can assign any number of users to that role, as it's appropriate.

this is one of my examples i post a lot: becasue you mentioned just read/write/select, i would think removing db_ddladmin would be what you are after.

Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

USE [WHATEVER]

Create USER [ClarkKent] FOR LOGIN [ClarkKent]

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? comment out if false

GRANT EXECUTE TO [AlmostOwners]

--allow the users to see view proc and function definitions

Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

--finally add our user to the role:

EXEC sp_addrolemember N'AlmostOwners', N'ClarkKent'

--test:

EXECUTE AS USER='ClarkKent'

--who am i?

select suser_name()

--do stuff

--change back into superman

REVERT;

--clean up after ourselves

/*

DROP ROLE [AlmostOwners]

DROP USER [ClarkKent]

DROP LOGIN [ClarkKent]

*/

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!

SQListic SSCertifiable Points: 6904 More actions · Answer 2

Great! This really helped me a lot lot lot.

Thank you:)

Lowell SSC Guru Points: 323518 More actions · Answer 3

Sqlism (8/9/2012)
Great! This really helped me a lot lot lot.
Thank you:)

Excellent! Thank you for the feedback!

I like trying to provide examples, it's much easier to help folks visualize and adapt!

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!