Re: Generating code for Application Roles.

From: Mary Chipman (mchip@nomail.please)
Date: 03/26/03 

From: Mary Chipman <mchip@nomail.please>
Date: Tue, 25 Mar 2003 18:50:23 -0500

You can get the syntax for creating the approle out of Books Online.
If you want to generate a script to assign permissions to all objects,
you can take advantage of the Query Analyzer. Set the output pane to
text (not grid) and execute the following statement (where Approlename
is the name of your approle):

SELECT 'GRANT ALL ON ' + RTrim(name) + ' TO AppRoleName ' +
CHAR(13) + CHAR(10) + 'GO' FROM sysobjects WHERE type = 'U'

This will generate output that you can then paste into another query
pane, edit, and execute. type=U is for user tables.

-- Mary
MCW Technologies
http://www.mcwtech.com

On 25 Mar 2003 10:16:49 -0800, rgillim@vdh.state.vt.us (Roger Gillim)
wrote:

>I have a database with Application Roles that give permissions to
>tables, views, and stored procedures.
>
>I have made the code to create a copy of the structure of the
>database, but with out application roles.
>
>Assume I execute that code. I now have a database with no application
>roles set.
>
>I would like to generate the code for the Application role such that,
>when the code is executed, the application role would be added:
>(sp_addapprole), and permissions (Grant) set for all of the objects in
>the database.
>
>Using an existing database, how do I generate the code that creates
>the application role in the database, and then sets the permissions
>for the objects?
>
>Thanks.
>Rog Gillim
>Vermont Dept of Health
>Burlington VT 05402
>
>rgillim@vdh.state.vt.us

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • SQL 2000 Windows Authentication - Same User Multiple Groups
    ... view-level permissions such that we can permit/deny a database action ... Execute permission on UpdateResearch to only IT (and explicitly Denied ... Windows group and we have assigned the appropriate group permissions on ...
    (microsoft.public.sqlserver.security)
  • Re: Effective Permissions Error with Domain User
    ... I set the database compatibility to 2005. ... server profile trace and found that it was calling the Execute As User. ... This leads me to believe it is some sort of permissions issue. ... Did you get these database from SQL Server 2000 by using a RESTORE command? ...
    (microsoft.public.sqlserver.security)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)