Security Related Stored Procedures

1. Sp_addlogin – Create a new login in SQL Server Instance
Syntax : sp_addlogin login_name, password, [def db], [def language]
Ex : sp_addlogin ‘kalyan’, ‘kumar’, master

2. Sp_adduser – Creates a new user and mapped to login in the current database.
Syntax : sp_adduser ‘login_name’,’user_name_in_db’
Ex : use kalyandb;GO; sp_adduser ‘kalyan’,’kalyan’

3. Sp_addrolemember – Adds login to database role in the current database
Syntax : sp_addrolemember ‘role_name’,’login_name’
Ex : sp_addrolemember ‘db_owner’, ‘kalyan’

4. Sp_changedbowner – changes owner of the current database
Syntax : sp_changedbowner ‘login name’
Ex : sp_changedbowner ‘kalyan’

5. Sp_defaultdb – changes the default database for any login
Syntax : sp_defaultdb login_name, database
Ex : sp_defaultdb ‘kalyan’,’master’

6. Sp_droprolemember – Removes server role of the specified login
Syntax sp_droprolemember role_name, login_name
Ex : sp_droprolemember ‘db_owner’, ‘kalyan’

7. Sp_dropuser – Removes user from the current database
Syntax : sp_dropuser ‘user_name
Ex: sp_dropuser ‘kalyan’

8. Sp_droplogin / drop login – Removes login from SQL Server Instance
Syntax : sp_droplogin loginname
Ex: sp_droplogin ‘kalyan’ (or) drop login kalyan