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

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

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

A non-privileged login account:

• Can see only the attributes of a login profile associated with the login (either directly or the default login profile).

• Cannot see the bindings of a login profile with login accounts.

Auditing

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

See also