How to give a user CRUD access to all Databases on a server

Question

How to give a user CRUD access to all Databases on a server

bdavey

Default port

Points: 1469
More actions
December 11, 2007 at 8:24 am

#181236

I have a special user who needs to ability to read, write, & modify any table or Stored Procedure across the entire server. What I do not what is the ability to modify logins, backup, & other admin stuff, so I think I want to avoid using server roles. I have tried the following with no success.
use [master]
GO
GRANT ALTER ANY DATABASE TO [BrianD]
GRANT CONNECT SQL TO [BrianD]
GRANT CREATE ANY DATABASE TO [BrianD]
GRANT VIEW ANY DATABASE TO [BrianD]
I have also tried
EXEC master..sp_addsrvrolemember @loginame = N'BrianD', @rolename = N'dbcreator'
Any ideas?

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello,

The simpleest way to have the user do all the required is to create a user role, bind this role with the required level fixed server role permissions and then bind the user to this role. With this way, you will be having the full control and also can add / deny permissions.

Hope this helps.

Thanks

Lucky

bdavey Default port Points: 1469 More actions · Answer 2

Sorry, Can you give a little more detail?

I think what your saying is to create a database role in each DB and give that role the permissions that I want this user to have. Then bind this user to this role.

If this is what your saying then this would not be much different (a little more work) than assigning the permissions that I want the user to have on each and every database. Since I have 150+ DB and more coming regularly I was hoping for a better route.

What am I missing?

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 3

What you're missing is that you need to be fiddling with DATABASE roles, not SERVER roles. Access to tables, stored procedures etc is governed by the database roles within each database.

As stated by lucky, the best way would be to create your own role within each database & add said user to the role. You can control the permissions better that way (and script it out the first time you do it, making subsequent times as easy as changing the connection & hitting F5).

Otherwise, you could try experimenting with adding the user to the db_datawriter, db_ddladmin database roles and see if that meets your needs (preferably not exceeding them).

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare