Professional Web Applications Themes

anyone help me with sp_setapprole - Microsoft SQL / MS SQL Server

Could anyone help me to sp_setapprole How is it used. Do I have SQL in Mixed Mode? Could anyone give me a little example of how is it done. Both sp_setapprole and sp_addapprole -- - Lars...

  1. #1

    Default anyone help me with sp_setapprole

    Could anyone help me to sp_setapprole

    How is it used. Do I have SQL in Mixed Mode?

    Could anyone give me a little example of how is it done.
    Both sp_setapprole and sp_addapprole
    --


    - Lars



    Lars Guest

  2. #2

    Default Re: anyone help me with sp_setapprole

    The SQL Server authentication mode has nothing to do with application roles.
    Check out my article at:
    http://www.microsoft.com/india/msdn/articles/74.aspx

    You will need to provide a passport account to access this article.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Lars Grøtteland" <no> wrote in message
    news:phx.gbl... 


    SriSamp Guest

  3. #3

    Default Re: anyone help me with sp_setapprole

    One little question about your aricle:

    You say that the user will have to reconnect to the database to enable the
    account once it has been deleted - deactivated.

    Is this so? Reconnectiong to a database is pretty time expending operation!

    --


    - Lars


    "SriSamp" <co.in> wrote in message
    news:O2Hf9$phx.gbl... 
    roles. 
    >
    >[/ref]


    Lars Guest

  4. #4

    Default Re: anyone help me with sp_setapprole

    Hi Lars,

    There is not obvious relationship between Mixed mode and sp_setapprole. Do
    not need to set SQL Server in Mixed mode.

    sp_setarpprole activates the permissions associated with an application
    role in the current database.

    Syntax
    sp_setapprole [rolename =] 'role' ,
    [password =] {Encrypt N 'password'} | 'password'
    [,[encrypt =] 'encrypt_style']

    Arguments
    [rolename =] 'role'
    Is the name of the application role defined in the current database. role
    is sysname, with no default. role must exist in the current database.
    [password =] {Encrypt N 'password'} | 'password'
    Is the password required to activate the application role. password is
    sysname, with no default. password can be encrypted using the ODBC
    canonical Encrypt function. When using the Encrypt function, the password
    must be converted to a Unicode string by preceding the password with N.
    [encrypt =] 'encrypt_style'
    Specifies the encryption style used by password. encrypt_style is
    varchar(10), and can be one of these values.
    None
    The password is not encrypted and is passed to Microsoft® SQL Server™ as
    plaintext. This is the default.
    Odbc
    The password is encrypted using the ODBC canonical Encrypt function before
    being sent to SQL Server. This can only be specified when using either an
    ODBC client or the OLE DB Provider for SQL Server. DB-Library clients
    cannot use this option.

    Return Code Values
    0 (success) and 1 (failure)

    This example activates an application role named SalesAppRole, with the
    plaintext password AsDeFXX, created with permissions specifically designed
    for the application used by the current user.
    EXEC sp_setapprole 'SalesApprole', 'AsDeFXX'


    sp_addapprole adds a special type of role in the current database used for
    application security.

    Syntax
    sp_addapprole [ rolename = ] 'role'
    , [ password = ] 'password'

    Arguments
    [ rolename = ] 'role'
    Is the name of the new role. role is sysname, with no default. role must be
    a valid identifier and cannot already exist in the current database.
    [ password = ] 'password'
    Is the password required to activate the role. password is sysname, with no
    default. password is stored in encrypted form.

    Return Code Values
    0 (success) or 1 (failure)

    This example adds the new application role SalesApp to the current database
    with the password xyz_123.
    EXEC sp_addapprole 'SalesApp', 'xyz_123'

    For additional information, please refer to the following articles on SQL
    Server Books Online:
    Topic "sp_addapprole"
    Topic "sp_setapprole"

    Hope it helps

    Regards,

    Michael Shao
    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.


    Michael Guest

  5. #5

    Default Re: anyone help me with sp_setapprole

    Yes. Once you activate an application role, there is no command to deactive
    it. So, you will need to reconnect to the database if you want the default
    permissions to be established. I would guess that you would not reconnect to
    the database often if you are using application roles. The idea behind
    application roles is that you want a special set of permissions for the
    user, irrespective of what was granted to the user. Therefore, you would
    execute all code under the context of the application role.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Lars Grøtteland" <no> wrote in message
    news:%23hs7mz%phx.gbl... 
    operation! 
    > roles. 
    > >
    > >[/ref]
    >
    >[/ref]


    SriSamp Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139