assigning db roles to a sql 2005 user

Question

Post reply

assigning db roles to a sql 2005 user

HBK_4700

SSCrazy Eights

Points: 8932
More actions
August 20, 2010 at 9:00 am

#141668

Hi,
I need to give below permissions on a db to a user, please let me know what db roles needs to be assinged for user.
Select permission
Insert update delete permission
backup and restore
execute stored procedure

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

something like this, maybe?:

CREATE ROLE [MyNewRole]

--Select permission (assuming every object, not specific tables)

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [MyNewRole]

--Insert update delete permission (assuming every object, not specific tables)

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [MyNewRole]

--backup and restore

ALTER AUTHORIZATION ON SCHEMA::[db_backupoperator] TO [MyNewRole]

--execute stored procedure (assuming every proc/view/function, not specific objects)

GRANT EXECUTE TO [MyNewRole]

--assign a user to the role containing the correct permissions.

EXEC sp_addrolemember N'MyNewRole', N'bob'

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!

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

Thank you very much.

2 more questions on sql security

1. Can we create a SQL login with out creating a windows user?

2. Can we create a DB user with out having a SQL Login?

Lowell SSC Guru Points: 323518 More actions · Answer 3

laddu4700 (8/20/2010)
Thank you very much.
2 more questions on sql security
1. Can we create a SQL login with out creating a windows user?

yes, of course. sql supports two kinds of loings, windows logins and sl logins.

stating the obvious, the user 'sa' is a sql login, having no windows account.

sql logins require the server to be set to mixed authentication mode, instead of the default of Windows only.

2. Can we create a DB user with out having a SQL Login?

yes. technically a user is tied to a specific database, where a login allows the user to connect.

the linking of a login to a username for a specific database gives them access to that database.

without a login, only commands using EXECUTE AS would be able to use the login-less user.

--instead of

--CREATE USER [bob] FOR LOGIN [bob]

CREATE USER [bob]

--or more understandibly:

CREATE USER [bob] WITHOUT LOGIN

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!