Security Information and Review

Collection of security information and review

Basic Microsoft SQL Server Security

Author : Admin

Every discuss about Microsoft Windows security and other security discussion, it’s most often described for the first time is the Authentication and Authorization. Authentication is the process of identification of the person / user and Authorization refers to the process of determining what that user can do. At this article, we will discuss about Microsoft SQL Server ( all version of MS SQL Server ), Authentication and Authorization.

 

In the Microsoft SQL Server, authentication process occures when someone wanted to make a connection to Microsoft SQL Server. User and password is required for some one to be able to access resources of the Microsoft SQL Server. Once the authentication process finishes, Microsoft SQL Server takes control of authorizing user’s can access to object ( Database, table, trigger and function ) and data in the system.

 

Microsoft SQL Server version 7.0 up, can grant permission to Basic Windows authenticated login on their individual account or the groups of which they are members SQL Server Authenticated. beside this rules, Microsoft SQL Server can grant permission based on the login ID or in membership in database roles, which function like Windows Groups.

To understand how the Authentication in MS SQL Server is integrated with MS Windows Authentication, we must understand the mechanism and process of Authentication on MS Windows itself. Basically when the user logged in the system, the system will be made by an access token containing the user’s Security Identifier (SID) and the list of all the group where users is member. Each time a user attempts to open a protected resource, Windows NT compares the SID and group memberships in the access token to the access control list (ACL), which lists approved users for that resource.

 

Microsoft SQL Server manages user account with a table named sysusers. This table identifies each user with a unique user identifier (UID), and each UID has a direct mapping to a server user identifier (SUID) in the syslogins table in SQL Server 6.5, or a security identifier (SID) from the sysxlogins table in SQL Server 7.0 and 2000.

 

The server roles are a fixed set of identities that a user can assume. They represent server administrator tasks and thus are rather narrow in their scope
in order to give you more granular control over what the administrators can do. I refer to sysadmin, serveradmin, securityadmin, and dbcreator as primary server roles, because they encompass the operations that most administrators will perform. In general, serveradmin, securityadmin, and dbcreator represent the subcategories of the system administrator’s overall duties. A judicious use of these primary server roles will allow the senior administrator to parcel out jobs according to skill level and expertise.

 

Sysadmin, this is the role from which the sa account gets all its abilities. In previous versions of SQL Server, sa was a special account for which security checking was bypassed. In version 7.0, the sa account became a member of the sysadmin server role. The sa account is unique in that it cannot be removed from the sysadmin role. The sysadmin role can bestow the same privileges on its other members as it does on the sa account. SQL Server accounts, Windows NT accounts, and Windows NT groups can all be members of this role. In fact, by default the local Windows NT Administrators group for the server is granted membership in the sysadmin role during setup. If your server is also a member of a domain, it is very likely that the Domain Admins global group is a member of the Administrators local group, which means that all domain administrators will have sysadmin privileges on the server.

 

ServerAdmin, this server role confers upon its members the ability to set both server configuration options and table options. Although SQL Server 7.0 automatically configures most of the main settings, there will be times when you need to change some of them. For example, to optimize SQL Server’s performance when other BackOffice applications are on the same server, you may need to change the minimum and maximum amount of memory SQL Server uses. Essentially, members of serveradmin have permission to run the sp_configure stored procedure. Some settings only take effect after you stop and restart the MSSQLSERVER service; others can take effect after you run the RECONFIGURE statement. This statement takes no parameters and simply activates the changes you made with sp_configure. The serveradmin role has several other miscellaneous permissions besides being able to run sp_configure. It can drop extended stored procedures, although it cannot create them; it can run the sp_tableoption stored procedure in all databases; and it can run the DBCC printable command.

 

SecurityAdmin, the securityadmin role is perhaps the second most important server role, because its members may run all security-related system stored procedures, including sp_addlogin and sp_grantlogin, as well as GRANT, DENY, or REVOKE permissions to create databases. It is also the only role besides sysadmin that may read the SQL Server error log, although anyone with access to the file system or xp_cmdshell can read this. The securityadmin role limits who can create a database by controlling the permission to run the CREATE DATABASE statement. By granting this statement permission, securityadmin effectively controls who can create databases on the server. Notice, however, that the permission does not extend to the DROP DATABASE statement. Only the database owner and members of the sysadmin role may drop a database.

 

DBCreator, this role may seem as though it duplicates one of the permissions of the securityadmin role, but it really has a different purpose. This role is designed for users who will be managing their own databases because it allows them to run not only the CREATE DATABASE statement but also the ALTER DATABASE statement. It also allows them to rename their databases using the sp_renamedb stored procedure. The primary difference between the dbcreator and securityadmin roles is that dbcreator is the only role other than sysadmin that has permission to run the ALTER DATABASE command. Members of the securityadmin role can grant users permission to create a database, but the user cannot alter the database’s size, change the location of its files, or add files or file groups. Members of the dbcreator role can make those changes as well as changes to the automatic filegrowth setting.

 

 

Literature:

  1. Microsoft SQL Server Security.
  2. Microsoft SQL Server Manual.
Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • Netvouz
  • DZone
  • ThisNext
  • MisterWong
  • Wists
  • Technorati
  • YahooMyWeb
  • Slashdot
  • StumbleUpon
Categories: SQL Server Security
I was just now googling around about this when I stumbled on your blog post. I'm simply visiting to say that I definitely enjoyed reading this post, it is very clear and well written. Are you planning topost more on this? It appears like there's more material here for later posts.
24 May 09 at 03:00
Hello. I think the article is really interesting. I am even interested in reading more. How soon will you update your blog?
17 June 09 at 02:05
thanks for your visit and support.
18 June 09 at 11:38
It's a pity that people don't realize the importance of this information. Thanks for posing it.
7 July 09 at 03:38
You you could make changes to the post title Basic SQL Server Security | SQL Server Security to more catching for your blog post you make. I loved the post nevertheless.
30 October 10 at 17:27
If you like this posts, please leave messages / comments.