Assign DML, DDL permission to login but not alter login, backup-restore, drop dbs

Question

Post reply

Assign DML, DDL permission to login but not alter login, backup-restore, drop dbs

bharat sethi

SSCommitted

Points: 1831
More actions
August 2, 2012 at 8:01 am

#279809

Dear All,
Hi! I need to create a login that has access on more than 10 databases.
This login can perform any DML, DDL operations (i.e. Select, Insert, Delete and Create, Drop SQL Objects etc.).
I need to restrict this login to not take the backup, restore databases, create or alter logins. Alter his/her own login (especially Database role membership).
I tried to achieve the same by assigning db_Datawriter and db_ddladmin role membership. But, after assigning these roles; this login is not able to
Select data from existing tables, alter tables from GUI, and Create new tables from GUI.
Hence, I assigned "db_Owner" role membership to this login. Then, he can do all above stuff but also able to change his "role membership".
I goggled a lot but, not find any solid solution.
Please guide how I achieve the same from GUI as well as by t-sql scripts. As I need to this for more than 20 users. Basically, all these users are developers.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

here's my suggestion:

create this role in each of the ten databases in question.

add the logins of the developers as users to each of the ten databases.

add the users to this role.

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!

bharat sethi SSCommitted Points: 1831 More actions · Answer 2

Dear one,

Thanks for the quick response. But, can you please explain why we are adding a new role and then adding this role under "db_ddladmin", "db_datareader", "db_datawriter" roles?

Need to understand why we are doing this......

Lowell SSC Guru Points: 323504 More actions · Answer 3

creating a custom role encompasing the specific requirements is a best practice.

permissions should be assigned to custom roles, and users assigned to the custom roles.

This makes migrating users from one role to another much easier.

assigning built-in roles directly against a user generates additional overhead. it's not considered a best practice.

for example: supposed you hire a new developer? or the this developer is replacing an exisitng

it is much easier to assign the user to this single role, which already has the permissiosn determined.

otherwise, you have to investigate And script out the permissions assigned to "bob" so you can add the new develoepr "jeff" with the same roles.

or what about the situation where the user "techwriter" is now promoted to developer? you have to strip his exisitng rights, then add the rights that are good for a developer?

much easier to move that person in and out of custom roles instead.

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!

bharat sethi SSCommitted Points: 1831 More actions · Answer 4

Dear One,

I got your point. To avoid overhead we are creating a new role as per our requirement. But, for a while if we not do so and assign all these 3 existing roles to the new Login then why it is not working & locking the things as mentioned in the starting post.

After implementing the process mentioned by you new login can do all the operations as I required. But, again when we are creating a table or altering existing table from GUI getting following errors: -

At the time I clicked New Table or Design table: -

You are not logged on as the database owner or system administrator. You might not be able

to save changes to tables that you do not own.

At the time I tried to save New Table or alter table: -

Warnings were encountered during the pre-save validation process, and might result in a

failure during save. Do you want to continue attempting to save?

Lowell SSC Guru Points: 323504 More actions · Answer 5

this warning is for the least common denominator thing:

the Object Explorer warned you if you are not in the db_owner role, but if you test my role, you will see that you can still create and save tables via the GUI; test it and try it for yourself: i just did.

you would not get the warning messages if you are creating tables via script (CREATE TABLE Table_1....

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!

bharat sethi SSCommitted Points: 1831 More actions · Answer 6

Dear,

Yes, I tried it & got successfully done. But, please reply on this: -

for a while if we not do your mentioned process and assign all these 3 existing roles to the new Login then why it is not working & locking the things as mentioned in the starting post.

Lowell SSC Guru Points: 323504 More actions · Answer 7

well, based on your post, you said the users cannot select, and i'm guessing that is because you did not assign db_datareader.

you'd have to report the specific error message they get for each operation to be sure what the actual issue is;

I just put together a role that does what you requested so far, and in my limited testing it works for me.

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!

bharat sethi SSCommitted Points: 1831 More actions · Answer 8

Dear,

OK. I did not add this login under db_Datareader role that is the reason he is not able to perform SELECT statement.

But, I added this user under "db_Datawriter" and "db_ddladmin" roles & I think "db_Datawriter" role is above the "db_Datareader" role. After this why login was not able to perform SELECT statement & I also added this login under "db_ddladmin" role. But, login was not able to Create/Drop tables.

Please guide what was the issue?

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 9

for a while if we not do your mentioned process and assign all these 3 existing roles to the new Login then why it is not working & locking the things as mentioned in the starting post.

Most probably you were not able to run SELECT statements using db_datareader or db_datawriter role because you did not run CREATE USER after Creating LOGIN. You were able to run SELECT statements using db_owner statements because you do not need to run CREATE LOGIN statement for that.

You have to run
Create USER [ClarkKent] FOR LOGIN [ClarkKent]

for each database for the user to run SELECT statements for db_datareader to be effective.