sp_securityprofile

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.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage any login profile` privilege.
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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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