Basic SQL Administration

            Since I was mainly into Windows AD and VMware administration, I was very much reluctant in learning MS SQL administration (Eventhough it comes under the task of a Windows administrator).

But now I think it is high time for me to learn at least some of the basics of SQL administration. This blog covers some of the basic SQL stuffs. I myself consider this blog as an incomplete blog and I will be updating this blog when I get something new and interesting.

Fixed Database Roles

Fixed database roles are defined at the database level and exist in each database.

The members of this role can add or remove access for Windows logins, Windows groups, and SQL Server logins.

Members of this role can backup the database.

Members of this role can run a SELECT statement against any table or view in the database.

Members of this role can add, delete, or change data in all user tables.

Members of this role can run any Data Definition Language (DDL) command in a database.

Members of this role cannot read any data in the user tables within a database

Members of this database role cannot add, modify, or delete any data in the user tables within a database.

Members of this role can perform all configuration and maintenance activities on the database.

Members of this role can modify role membership and manage permissions.

Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role.

Difference between sysadmin role and dbo

The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.
Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo.

How to give permission for a user in database

When you need to add a new user to database:

  • Create a login for the user in SQL server level and then to database level.

USE [master]

  • Create a user in DB

SET @SQL = 'CREATE USER [‘<account name>'] FOR LOGIN [‘<account name>']';

  • Grant permissions for the user (eg: read, write and membership control rights)

EXEC sp_addrolemember N'db_datareader', ‘<account name>'
EXEC sp_addrolemember N'db_datawriter', ‘<account name>'
EXEC sp_addrolemember N'db_securityadmin', ‘<account name>'

To find the effective permission of a user in a DB

Execute the below in a DB:

execute as user = ‘<Username>'
select * from fn_my_permissions(null, 'DATABASE') -- Leave these arguments, don't change to MyDatabaseName
order by subentity_name, permission_name

How to kill a process in SQL

This will come handy when you need to delete a database some of the existing connections to that DB denies you from the action,

Ø  Execute the command sp_who to find the list of connections to the SQL server.
Ø  Take a note of the spid of the processes which uses the particular DB.
Ø  Execute the command kill <spid> where spid is the value noted in the above step.


Popular posts from this blog

VMware and Windows Interview Questions: Part 2

VMware and Windows Interview Questions: Part 3

VMware vMotion error at 14%