Wednesday, October 10, 2012

Some stored procedure related to security

Sp_addrolemember : Adds login to database role in the current database
Syntax : sp_addrolemember 'role_name','login_name'
eg : sp_addrolemember 'db_owner', 'akl'

Sp_droprolemember : Removes server role of the specified login
Syntax sp_droprolemember role_name, login_name
eg : sp_droprolemember 'db_owner', 'akl'

Sp_changedbowner : changes owner of the current database
Syntax : sp_changedbowner 'login name'
eg : sp_changedbowner 'akl'

Sp_addlogin : Create a new login in SQL Server Instance
Syntax : sp_addlogin login_name, password, [def db], [def language]
eg : sp_addlogin 'akl', 'kumar', master

Sp_adduser : Creates a new user and mapped to login in the current database.
Syntax : sp_adduser 'login_name','user_name_in_db' 
eg  : use akldb;GO; sp_adduser 'akl','akl' 

Sp_defaultdb : changes the default database for any login
Syntax : sp_defaultdb login_name, database
eg : sp_defaultdb 'akl','master'

Sp_dropuser : Removes user from the current database
Syntax : sp_dropuser 'user_name
eg : sp_dropuser 'akl'

Sp_droplogin / drop login : Removes login from SQL Server Instance
Syntax : sp_droplogin loginname
eg : sp_droplogin 'akl' (or) drop login akl

No comments:

Post a Comment