CODEWITHSUNDEEP
sql-serversql-server-2005ado.net
HCL
HCL

Reputation: 36775

Grant permissions to an application role in SQL Server 2005

I have to setup a .net-application that connects to an SQL Server 2005 in a new database. The app uses an application role to authenticate on the server and handles then all security-checks in the app.

I have successfully created the application role and the app can successfully logon to the db. However, the role has no permissions to query any database-object.

How do I assign the roles db_datareader and db_datawriter to the application role so that it has full read-and write access to all tables of the database? Or is there another way to give the role full access to the tables, queries and stored-procedures.

(The app was developped on sql-server 2000, however there are already running instances of it with SQL Server 2005 but I have not seen how it is done that the application role has the desired rights.)

Please note, giving the app role manualy access to every object is not an option because the app creates dynamicly new objects (tables, queries and sp's) and the app must also have access to these new objects without change the roles security configuration.

Upvotes: 2

Views: 3080

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

For reading:

GRANT SELECT TO application_role

For writing:

GRANT INSERT TO application_role
GRANT UPDATE TO application_role
GRANT DELETE TO application_role

Upvotes: 4

Related Questions