Enterprise Service Cloud - Docs

SSMS Actions

Overview

In this section we describe the actions which you can execute directly on a SQL instance, i.e. through a SSMS or SSDT connection. Please, refer to the (Permissions Model) description for allowed actions on a SQL instance.

Basically a member of the customerAdmin server role can execute any actions through SSMS/SSDT GUI or T-SQL statement which are allowed through the permission model such as Create, Delete, Modify databases, logins, users or create SQL jobs.

Other users will be able to execute the actions at database level according to the permissions granted through members of the customerAdmin server role.

To get an overview of the capabilities read the chapter (Server settings Configuration).

MS SQL DBMS DBA Mode

The MS SQL DBMS DBA mode presents the same day 2 actions in form of stored procedure, vRA inputs or APIs as the MS SQL DBMS managed. Due to the extended rights, the behaviour of some of the day 2 actions has been changed accordingly. Despite the fact the stored procedure inputs and calls are identical, the result of the actions will be slightly different.

The table below summarize the specific stored procedures and how the results differ against the ones on MS SQL DBMS managed service. All the other stored procedures behave in the same way as on MS SQL DBMS managed.

Stored procedureChange
sp_createAdminCreate SQL login member of the sysadmin role
sp_createWindAuthAdminAdd Windows login member of the sysadmin role
sp_addAdminGrant existing login with sysadmin membership
sp_grantSrvRoleGrant role to any built-in server role
sp_revokeSrvRoleRevoke role from any built-in server role
sp_revokeAdminRemove login from sysadmin role
sp_setTraceFlagSet any trace flag
sp_disableTraceFlagDisable any trace flag
sp_LoadAssemblyLoad CLR assembly from file
sp_dropCertificateDrop assembly for certificate signing
sp_LoadAssemblyLoad assembly for certificate signing

Usage scs_AdminDB stored procedures

The stored procedure are part of the permission model and capabilities for the customer logins with administration rights; see description "Description Customer Version".

Below you will find an explanation of the stored procedure's purpose along with their usage and examples.

sp_addAdmin

Description
Adds an existing SQL or Windows login to the customerAdmin role.

Syntax

sp_addAdmin [ @loginName ] 'login'

Arguments

[@loginName = ] existing login, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_addAdmin @loginName = myLogin
for windows login
EXEC customer.sp_addAdmin @loginName = [domain\mylogin]

sp_createAdmin

Description
Creates a new SQL login to the server role customerAdmin.

Syntax

sp_createAdmin [ @adminName= ] 'login'
    [ , [ @password = ] 'password' ]

Arguments

[@adminName = ] Name of your choice for the customer DBA, sysname.
[@password = ] Password of your choice, varchar(20).

Remarks
For the password parameter the option CHECK_POLICY is set to on.

Example

USE scs_adminDB
GO
EXEC customer.sp_createAdmin @adminName = myLogin, @password = 'myStrongPassword'

sp_createNewDB

Description
Create a new database and a login mapped to it with db_owner rights. The database files are create according to the number of CPU cores detected on the system (max. 8).

The login name is set as dbo_dbname and will be immediately enabled. You can log in with this user and delete it if not needed.

Syntax

sp_createNewDB [ @dbName= ] 'database name'
    [ , [ @dataSize = ] 'data file initial size' ]
    [ , [ @dataMaxSize = ] 'data file max size' ]
    [ , [ @dataGrowth = ] 'data file growth size' ]
    [ , [ @logSize = ] 'transaction log initial size' ]
    [ , [ @logMaxSize = ] 'transaction log max size' ]
    [ , [ @logGrowth = ] 'transaction log growth size' ]
    [ , [ @password = ] 'password' ]

Arguments

[@dbName= ] Name of your choice for the customer DBA, sysname. → Space and hyphen are not valid characters in the database name
[@dataSize = ] Data file initial size in MB, cannot be 0, integer.
[@dataMaxSize = ] Data file max size in MB, must be higher than @datasize, cannot be 0, integer.
[@dataGrowth = ] Data file growth in MB, cannot be 0, integer.
[@logSize = ] Transaction log initial size in MB, cannot be 0,, integer.
[@logMaxSize = ] Transaction log initial size in MB, cannot be 0, must be higher than @logSize, integer.
[@logGrowth = ] Transaction log initial size in MB, cannot be 0, integer.
[@password = ] Password of your choice, varchar(20).

Remarks

For the password parameter the option CHECK_POLICY is set to on.
If the login already exists on the target system an error will be returned.
If you get the following error 1802 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Check please your data or log inputs.

Example

USE scs_adminDB
GO
EXEC customer.sp_createNewDB @dbName = 'myDB', @dataSize = 400, @dataMaxsize = 1000, @dataGrowth = 100, @logsize = 200, @logMaxSize = 500, @logGrowth = 50, @password = 'YouRPass_123'

sp_createWindAuthAdmin

Description
Creates a Windows login from domain user or group and adds it to the server's customerAdmin role.

Syntax

sp_createWindAuthAdmin [ @adminName= ] 'login'

Arguments

[@adminName = ] domain user or group, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_createWindAuthAdmin @adminName = [domain\mylogin]

sp_disableTraceFlag

Description
Disables a previously set trace flag.

Syntax

sp_disableTraceFlag [ @traceFlag= ] 'trace flag'

Remarks

Trace flag 1204, 1222 are allowed.

Arguments

[@traceFlag= ]  trace flag number, int.

Example

USE scs_adminDB
GO
EXEC customer.sp_disableTraceFlag @traceFlag = 1204

sp_grantAgentJob

Description
Grant membership to the SQLAgentUserRole msdb role.

Syntax

sp_grantAgentJob [ @loginName = ] 'login'

Arguments

[@loginName= ]  existing login, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_grantAgentJob @loginName = 'mylogin'

sp_grantSrvRole

Description
Grants server role bulkadmin or setupadmin to an existing login.

Syntax

sp_grantSrvRole [ @loginName= ] 'login'
    [ , [ @srvRole = ] 'server role' ]

Arguments

[@loginName = ] existing login, sysname.
[@srvRole = ] server role, sysname.

Remarks
Bulkadmin or setupadmin are allowed.

Example

USE scs_adminDB
GO
EXEC customer.sp_grantSrvRole @loginName = myLogin, @srvRole = 'bulkadmin'

sp_revokeAdmin

Description
Removes an existing SQL or Windows login from the customerAdmin role.

Syntax

sp_revokeAdmin [ @adminName ] 'login'

Arguments

[@adminName = ] existing login, sysname.

Remarks
The login will be just removed form the role, but not deleted.

Example

USE scs_adminDB
GO
EXEC customer.sp_revokeAdmin @loginName = myLogin
or windows login
EXEC customer.sp_revokeAdmin @loginName = [domain\mylogin]

sp_revokeAgentJob

Description
Revokes membership for a login from the SQLAgentUserRole msdb role.

Syntax

sp_revokeAgentJob [ @loginName = ] 'login'

Arguments

[@loginName= ]  existing login, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_revokeAgentJob @loginName = 'mylogin'

sp_revokeSrvRole

Description
Revokes server role bulkadmin or processadmin to an existing login.

Syntax

sp_revokeSrvRole [ @loginName= ] 'login'
    [ , [ @srvRole = ] 'server role' ]

Arguments

[@loginName = ] existing login, sysname.
[@srvRole = ] server role, sysname.

Remarks
Bulkadmin or processadmin are allowed.

Example

USE scs_adminDB
GO
EXEC customer.sp_revokeSrvRole @loginName = myLogin, @srvRole = 'bulkadmin'

sp_grant_ViewSrvState

Description
Grants View Server State permission to a login.

Syntax

sp_grant_ViewSrvState[ @loginName = ] 'login'

Arguments

[@loginName= ]  existing login, sysname.

Example

USE scs_adminDB
GO
exec customer.sp_grant_ViewSrvState @loginName =  'mylogin'

sp_revoke_ViewSrvState

Description
Revokes View Server State permission to a login.

Syntax

sp_revoke_ViewSrvState[ @loginName = ] 'login'

Arguments

[@loginName= ]  existing login, sysname.

Example

USE scs_adminDB
GO
exec customer.sp_revoke_ViewSrvState @loginName =  'mylogin'

sp_setTraceFlag

Description Enables server's trace flag.

Syntax

sp_setTraceFlag  [ @traceFlag= ] 'trace flag'

Remarks
Trace flag 1204, 1222 are allowed.

Arguments

[@traceFlag= ]  trace flag number, int.

Example

USE scs_adminDB
GO
EXEC customer.sp_setTraceFlag  @traceFlag = 1204

sp_statusTraceFlag

Description
Returns server's trace flag status, i.e. if trace flag is enabled.

Syntax

sp_setTraceFlag

Arguments None

Example

USE scs_adminDB
GO
EXEC customer.sp_statusTraceFlag
Output Sample

sp_viewSqlLog

Description
Reads the SQL error log, passes parameter to switch between different logs and filters the result. If no parameters are passed, the current log will be returned as default. This procedure execute in background the extended procedure xp_readerrorlog.

Syntax

sp_viewSqlLog [ @errorLogNumber= ] 'number of error log'
    [ , [ @logType= ] 'type of log' ]
    [ , [ @Filter1Text= ] 'text to be searched' ]
    [ , [ @Filter1Text= ] 'additional text to be searched' ]
    [ , [ @FirstEntry = ] 'start date' ]
    [ , [ @LastEntry = ] 'end date' ]
    [ , [ @sortOrder = ] 'ascending or descending' ]

Arguments

[@errorLogNumber = ] NULL or 0 for current log, 1 for the log before last restart, 2 for older than 1 etc., int.
[@logType = ] NULL or 1 for SQL Error Log, 2 for SQL Agent Log, int.
[@Filter1Text= ] free text for searching, nvarchar(4000).
[@Filter2Text= ] additional free text for searching, nvarchar(4000).
[@FirstEntry = ] start date to restrict search in a specific period, datetime.
[@LastEntry = ] end date to restrict search in a specific period, datetime.
[@SortOrder = ] pass 'asc' or 'desc', nvarchar(4).

Example
This execution will return the SQL error log before the last restart sorted asc.

USE scs_adminDB
GO
EXEC customer.sp_viewSqlLog
@errorLogNumber = 1,
@logType = 1,
@Filter1Text = NULL,
@Filter2Text = NULL,
@FirstEntry = NULL,
@LastEntry = NULL,
@SortOrder = 'asc'

sp_CollectInstanceInfos

Description
Fill update system information in order to select from the views VW_DisplayDbFileInformation, VW_DisplayDbRolesMembers, VW_DisplayDbUserPermissions, VW_DisplayDbUsers.

Syntax

sp_CollectInstanceInfos

Arguments
None

Example

USE scs_adminDB
GO
EXEC dbo.sp_CollectInstanceInfos

sp_LoadAssembly

Description Creates an assembly from a dll file into a specific database.

Syntax

sp_LoadAssembly[ @assemblyName ] 'assembly name'
[ , [ @dbName = ] 'database name' ]
[ , [ @assemblyFileName= ] 'assembly file name' ]

Arguments

[@assemblyName = ] assembly object name for database, sysname
[@dbName = ] target database name, sysname
[@assemblyFileName= ] name of assembly dll file, varchar(200)

Example

USE [scs_AdminDB]
GO 

EXEC  [Customer].[sp_LoadAssembly]  @assemblyName = myAsm, @dbName = mytest, @assemblyFileName = 'myAsm.dll'
GO

sp_dropCertificate

Description Drop a previously uploaded certificate for assembly signing.

Syntax

sp_dropCertificate

Arguments none

Example

USE [scs_AdminDB]
GO

EXEC  @return_value = [Customer].[sp_dropCertificate]
GO

sp_LoadCertificate

Description Create a certificate for assembly signing in the target DBMS.

Syntax

sp_LoadCertificate[ @certificateName ] 'certificae name'
[ , [ @certFileName = ] 'certificate file name' ]

Arguments

[@certificateName = ] assembly object name for database, sysname
[@certFileName = ] name of certificate cer file, varchar(200)

Example

USE [scs_AdminDB]
GO 

EXEC  [Customer].[sp_LoadCertificate] @certificateName = myCertificate, @certFileName = 'testcertificate.cer'
GO

SQLAO actions

sp_AO_addDBtoAG

Description
Add a database to a specific availability group and synchronize it to the replica. Execute the SP on the primary, otherwise an error will be returned.

Syntax

sp_AO_addDBtoAG [ @dbName = ] 'DB name'  
[ , [ @agName= ] 'AG name' ]

Arguments

[@dbName = ] existing database, sysname.
[@agName = ] existing availability group, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_AO_addDBtoAG @dbName = 'myDB', @agName = [AG-L01]

sp_AO_moveAG

Description
Move a specific availability group to the replica. Execute the SP on the replica, otherwise an error will be returned.

Syntax

sp_AO_moveAG [ @agName= ] 'AG name' ]

Arguments

[@agName = ] existing availability group, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_AO_moveAG @agName = [AG-L01]

sp_AO_fixLogin

Description
Fix access of a SQL login to a specific database member of a specific availability group. This action is needed after a move group and it apply to manually created SQL login on the replica server having a different SID.

Syntax

sp_AO_fixLogin [ @dbName = ] 'DB name'  
[ , [ @agName= ] 'AG name' ]
[ , [ @loginName= ] 'login name' ]

Arguments

[@dbName = ] existing database, sysname.
[@agName = ] existing availability group, sysname.
[@loginName = ] existing sql login mapped to the database, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_AO_fixLogin @dbName = 'myDB', @agName = [AG-L01], @LoginName = mySqlLogin

sp_AO_removeDBfromAG

Description
Remove a database from a specific availability group. The database on replica will be dropped. The database on primary will remain. Execute the SP on the primary, otherwise an error will be returned.

Syntax

sp_AO_removeDBfromAG [ @dbName = ] 'DB name'  
[ , [ @agName= ] 'AG name' ]

Arguments

[@dbName = ] existing database, sysname.
[@agName = ] existing availability group, sysname.

Example

USE scs_adminDB
GO
EXEC customer.sp_AO_removeDBfromAG @dbName = 'myDB', @agName = [AG-L01]
Last Updated: