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

Precedence rules are followed for attributes no set in profiles. For more information.

Permissions

The permission checks for sp_securityprofile differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with manage any login profile privilege.

Granular permissions disabled

With granular permissions disabled, you must be a user with sso_role to see attributes and bindings of all login profiles.

For a non-privileged login account:

You can only see the attributes of a login profile associated with the login (either directly or the default login profile).
You cannot see the bindings of a login profile with login accounts.

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