Creating security roles

Hello,

I have users with different roles accessing my system.

For eg, for database Adventure:

Role 1 - Can only read write data but not update

Role 2 - Can read, write and update

Role 3 - Can search data and run reports

Role 4 - Can backup, create users, replication, Run SSIS.

How do I create scripts for such users.

Regards,

Vidya.

Do it the easy way. Create the roles in the GUI and then hit the "Script" button at the top.

And you may want to grant permissions to the schemas, so that new tables you add will have the right permissions.

Rob|||

Look up Server, Integration Services andDatabase Level roles in books online.

This should get you started:

Role 1 - Can only read write data but not update db_datareader

Role 2 - Can read, write and update db_datawriter and db_datareader

Role 3 - Can search data and run reports db_datareader, run reports is outside of T-SQL jurisdiction. Try the Reporting Services forum

Role 4 - Can backup - BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

create users, requires ALTER ANY USER rights, or db_owner

replication, Not sure, might be CONTROL SERVER server privilege or sysadmin,

Run SSIS db_dtsadmin, db_dtsltduser, and db_dtsoperator

|||thanks

Creating roles

Hello,

I know there is sp_addrole and sp_droprole to create roles. I just
found out that I could not use these two statements in an application
because the application is interacting with the database within
transactions through ODBC (SQLServer complains that these two stored
procedures cannot be executed within transactions).

Is there an alternative way of creating and dropping roles in plain
SQL?Hi

Adding and removing roles is not something that you should need to do
dynamically, but is more of an installation task. You may want to add people
to roles, but usually those roles are fixed.

Not being able to add a role within a transaction is documented in Books
Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_addp_33s5.htm

I suggest you find out when/why your trasactions are started and why you are
trying to create a role at that time! To do this you may want to use
profiler.

John

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0405311620.4fa9a98b@.posting.google.c om...
> Hello,
> I know there is sp_addrole and sp_droprole to create roles. I just
> found out that I could not use these two statements in an application
> because the application is interacting with the database within
> transactions through ODBC (SQLServer complains that these two stored
> procedures cannot be executed within transactions).
> Is there an alternative way of creating and dropping roles in plain
> SQL?