sp_securityprofile

Description

Lists the attributes or bindings associated with a login profile.

Syntax

sp_securityprofile ‘attributes’,‘login profile’,
	{wildcard | login_profile_name | ‘default’}
sp_securityprofile ‘bindings’, ‘login profile’
	[, {wildcard | login_profile_name | ‘default’} 
	[, ‘login’ ,{wildcard | login_name}]]
sp_securityprofile ‘help’ 

Parameters

attributes

specifies to list attributes associated with a login profile.

login profile

specifies to obtain information about login profiles.

bindings

when login is specified, list binding of login accounts. When login profile is specified, list bindings of login profiles.

login

specifies to obtain information about login accounts.

wildcard | login_profile_name | default

specifies the login profile in which to obtain information. Options include a specific a name of a login profile, the default login profile, or wildcard characters can be used identify login profiles.

wildcard | login_name

specifies to use a specific login account name or allows the use of wildcard characters to identify login accounts.

help

displays usage.

Examples

Example 1

Lists all attributes of the default login profile.

sp_securityprofile 'attributes', 'login profile',
    'default'
Name                         Value 
------------                 ----------------
login profile                def_login_profile
default                      yes
default database             master
default language             NULL
login script                 NULL
auto activated roles         emp_role
auto activated roles         def_role
manually activated roles     special_role
authenticate with            ANY
track lastlogin              TRUE
stale period                 180D

Example 2

Displays all the attributes associated with all login profiles.

sp_securityprofile 'attributes', 'login profile', '%'
Name                         Value 
----------                   ----------------
login profile                def_login_profile
default                      yes
default database             master
default language             NULL
login script                 NULL
auto activated roles         emp_role
auto activated roles         def_role
authenticate with            ANY
track lastlogin              TRUE
stale period                 180D

Name                         Value 
-----------                  ----------------
login profile                eng_login_profile
default                      
default database             work
login script                 engr_script
auto activated roles         emp_role
auto activated roles         def_role
auto activated roles         engr_role
authenticate with            LDAP

Name                         Value 
------------                 ----------------
login profile                mgr_login_profile
default                      
default database             work
login script                 mgr_script
auto activated roles         emp_role
auto activated roles         def_role
auto activated roles         mgr_role
manually activated roles     activate_emp_role
authenticate with            LDAP

Name                         Value 
-------------                ----------------
login profile                sa_login_profile
manually activated roles     admin_role
default

Example 3

Displays all login accounts associated with a specific login profile.

sp_securityprofile 'bindings', 'login profile',
    'engr_login_profile'
Login name          Login profile name
-----------         ----------------
anderson            eng_login_profile
gupta               eng_login_profile
lchang              eng_login_profile
tsato               eng_login_profile

Example 4

Displays the login profile for the login account named sa.

sp_securityprofile 'bindings', 'login profile', null, 
    'login', 'sa'
Login name          Login profile name
-----------         ----------------
sa                  sa_login_profile

Usage

Permissions

sso_role is required to see attributes and bindings of all login profiles.

A non-privileged login account:

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands creat login profile, alter login profile

Documentation , “Applying login profile and password policy attributes,” in the Security Administration Guide

System procedures sp_displaylogin