Any way to generate SQL Script for application role?

Is there any way to generate the SQL to create an application role and all t
he associated grants for table and stored procedure access? Or does anyone h
ave a suggestion for how to migrate an application role from one environment
to another? The developers
originally create the role by clicking on things.The easiest way is to script the existing application role permissions
and edit the script for the new environment. See sp_addapprole in SQL
BOL for the syntax for creating a new application role.
--Mary
On Fri, 16 Apr 2004 10:01:10 -0700, Charlotte
<anonymous@.discussions.microsoft.com> wrote:

>Is there any way to generate the SQL to create an application role and all the asso
ciated grants for table and stored procedure access? Or does anyone have a suggestio
n for how to migrate an application role from one environment to another? The develo
per
s originally create the role by clicking on things.sql

Any one used Application Role before?

I'm using vb6 and i'm trying to create a connection string using an
application role.
If this is possible.
I don't know what the string should look like or whether i have put
everything in place.
I'm using Windows Authentication Mode. However I created a Application Role
and password for my app. Should i use this to connect to the db so i don't
have to use a userid? I shouldn't need a windows login to use an application
.
According to my book: "After the application has enabled and application
role, all permissions of the user are suspended, and only the permissions of
the role are enforced." Then it says later: "The best part is that all
activity is still audited with the users' login information."
Microsoft said at
http://msdn.microsoft.com/library/d...
ahx.asp :
"Use the Integrated Security keyword, set to a value of SSPI, to specify
Windows Authentication (recommended),
or
use the User ID and Password connection properties to specify SQL Server
Authentication.
Security Note When possible, use Windows Authentication. If Windows
Authentication is not available, prompt users to enter their credentials at
run time. Avoid storing credentials in a file. If you must persist
credentials, you should encrypt them with the Win32? crypto API. For more
information, see "The Crypto API Function" in the MSDN? Library at this
Microsoft Web site."
I don't can't make sense of both of these statements. How do they work
together or do they contradict? What should i do?
Here is my connection String
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DatabaseName;Data Source=ServerName
I'm able to get it to work with my login using Windows Authenticity, but
everyone can't use my login name. I can't set permissions for all users
either.
Thanks!You still need both a login (SQL Server or Windows) and a user name in the d
atabase. Then you app
uses sp_setapprole to enter the application role. If you aren't familiar wit
h SQL Server security
architecture, read in Books Online on the topic (so you understand both the
login and the user name
concepts).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jacob" <jacob@.discussions.microsoft.com> wrote in message
news:68E272F5-7FD8-4D8C-B4B6-F8F2AEC7A70F@.microsoft.com...
> I'm using vb6 and i'm trying to create a connection string using an
> application role.
> If this is possible.
> I don't know what the string should look like or whether i have put
> everything in place.
> I'm using Windows Authentication Mode. However I created a Application Rol
e
> and password for my app. Should i use this to connect to the db so i don't
> have to use a userid? I shouldn't need a windows login to use an applicati
on.
> According to my book: "After the application has enabled and application
> role, all permissions of the user are suspended, and only the permissions
of
> the role are enforced." Then it says later: "The best part is that all
> activity is still audited with the users' login information."
>
> Microsoft said at
> http://msdn.microsoft.com/library/d...r />
_0ahx.asp :
> "Use the Integrated Security keyword, set to a value of SSPI, to specify
> Windows Authentication (recommended),
> or
> use the User ID and Password connection properties to specify SQL Server
> Authentication.
>
> Security Note When possible, use Windows Authentication. If Windows
> Authentication is not available, prompt users to enter their credentials a
t
> run time. Avoid storing credentials in a file. If you must persist
> credentials, you should encrypt them with the Win32? crypto API. For more
> information, see "The Crypto API Function" in the MSDN? Library at this
> Microsoft Web site."
>
> I don't can't make sense of both of these statements. How do they work
> together or do they contradict? What should i do?
> Here is my connection String
> Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DatabaseName;Data Source=ServerName
> I'm able to get it to work with my login using Windows Authenticity, but
> everyone can't use my login name. I can't set permissions for all users
> either.
> Thanks!
>

Any ideas, please...

I have create a database, login, role, user at a named instance of MSDE by
running the following scropt:
CREATE DATABASE [testdb_vg]
USE Master
EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
USE [testdb_vg]
EXEC SP_ADDROLE [test_role]
GRANT CREATE TABLE TO [test_role]
EXEC SP_ADDUSER [test_login], [test_user], [test_role]
Then I run SQL Analyser and try to connect to the database using SQL
Authentication:
user: test_user
password: psw
and I am getting message:
Unable to connect to server SSSSSS\NNNN
Message: 18452, level 16, state 1,
[Microsoft][ODBC SQL Server Driver][SQL Server] Login for user "test_user".
Reason: not associated with a trusted SQL Server connection
with other SQL server instance it works OK?!Are you sure MSDE is set up to use both SQL Server and Windows
authentication? Sounds like it is set to Windows authentication only...
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||See the section that starts with "Another way to change the security mode
after installation..." in
http://support.microsoft.com/?kbid=285097
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||In my case MSDE was set up to use Windows authentication only...|||is there any SQL query way to recognize what the mode is for an instance of
SQL Server?