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.
db_accessadmin
The members
of this role can add or remove access for Windows logins, Windows groups, and
SQL Server logins.
db_backupoperator
Members of
this role can backup the database.
db_datareader
Members of
this role can run a SELECT statement against any table or view in the database.
db_datawriter
Members of
this role can add, delete, or change data in all user tables.
db_ddladmin
Members of
this role can run any Data Definition Language (DDL) command in a database.
db_denydatareader
Members of
this role cannot read any data in the user tables within a database
db_denydatawriter
Members of
this database role cannot add, modify, or delete any data in the user tables
within a database.
db_owner
Members of
this role can perform all configuration and maintenance activities on the
database.
db_securityadmin
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]
SET
@SQL = 'CREATE LOGIN ['< account
name>'] FROM WINDOWS WITH DEFAULT_DATABASE= [master]';
EXECUTE(@SQL);
- Create a user in DB
SET
@SQL = 'CREATE USER [‘<account
name>'] FOR LOGIN [‘<account
name>']';
EXECUTE(@SQL);
- 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
revert
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.
Comments
Post a Comment