Setting up a user to my database in my SQL Server

Question

Hi All,

I just finished creating a new user for my database in SQL Server. I had 4 tables I wanted to grant Insert, Update, Select and delete permissions. I did this manually in the Securables area of the new user.

Is there a better way to do this that to have to touch each object? if so, how?

Thanks,

rod.

Answer 1

One way is use schemas such that

• tables belong to a schema (let's call it data, CREATE SCHEMA)
• users belong to a role (CREATE ROLE, sp_addrolemember)
• permissions are assigned to the role on the schema (GRANT INSERT ON schema::data to myRole)

Now, you can add new tables or change users without losing/creating permissions

Answer 2

If you want finely granular control over who can do what, I don't think there's a whole lot you can do - you're doing it just fine.

gbn's approach is quite nifty - another approach for "simple" setups (when you don't need a whole lot of different permissions) is to:

• grant every user (or a role) the database role db_datareader - this allows read access (SELECT) on all tables and views

• grant every user (or a role) the database role db_datawriter - this allows write access (INSERT, UPDATE, DELETE) on all tables and views

If you also need to grant execution rights on stored procedures, there's unfortunately no predefined role to use. You can however create your own database role and then grant execute permissions to that role. The great thing is: this permission to execute stored procedures also applies to all future stored procedure you might create in your database!

To define your new role, use this:

CREATE ROLE db_executor
GRANT EXECUTE TO [db_executor]

and then you can just assign db_executor to those users who need to be able to execute stored procs and stored functions in your database.