Generating code for Application Roles.

From: Roger Gillim (rgillim@vdh.state.vt.us)
Date: 03/25/03 

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

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: New user with no permissions can see and execute system stored procedures...
    ... Best regards ... But I can still see system views and stored procedures (though not system ... and map it to a database user and set its default schema to dbo. ... I have only tried to execute sys.sp_catalogs, but in my opinion a new ...
    (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)