April 24, 2012 at 10:05 am
Dear All,
I've created a user and given them ”databasereader" and "databasewriter" permissions, but in addition I need them now to be able to do the following:
-Create/Delete/Update Tables, Create/Delete/Update Stored Procedures, Create Views/Delete/Update, Create Functions/Delete/Update
I was wondering if it is just easier to give them ddl_admin permission or is there another was I would be able to give them permission. Of course I'm not looking for the easiest was but the more secure process.
Thank you in advance!
April 24, 2012 at 11:07 am
You can't assign permissions to object types in SQL Server. But you could create a schema with only the procedures and tables you need to give permissions for and then grant the appropriate permissions to that schema.
Maybe others can offer a better solution. 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
April 24, 2012 at 11:22 am
If it needs to be done for many users, creating few roles would be a good idea.
April 24, 2012 at 11:39 am
The most secure is to be the most explicit. Create a custom role and define the necessary permissions to that role. Then add your user(s) to that role.
Here's an example:
use YourDatabase
go
create role your_custom_role
go
exec sp_addrolemember 'your_custom_role', 'YourUser'
go
grant create table to your_custom_role
go
grant alter on schema::dbo to your_custom_role
go
grant create procedure to your_custom_role
go
grant create view to your_custom_role
go
grant create function to your_custom_role
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply