SQL Server 2005 security basics

Who and what

  • Principles (new in SQL Server 2005) – users, groups, processes.
    • Windows Principal – user or group (local/domain login)
    • SQL Server Principal (SQL Server login)
    • Database Principal:
      • Database User (maps either to SQL Server or Windows principle)
      • Database Role (predefined set of permissions; role is assigned to user so he automatically gets these permissions)
      • Application Role (allows application to run with its own, user-like permissions, “access to specific data to only those users who connect through a particular application”, “Unlike database roles, application roles contain no members and are inactive by default, are enabled by using sp_setapprole, which requires a password.”
  • Securables – any resources or objects on SQL Server. Objects may contain other objects which creates an hierarchy of securables or scopes: Schema scope (table, view, …) -> database scope (user, schema, …) -> Server scope (login, database, …). This allows to simplify management of permissions for objects
  • Permissions – actions granted, revoke or denied to principals on securables


Named collection of DB objects which form a namespace (create objects and schemas, associate objects with schemas)
User vs. Schema: objects in 2005 have the following fully qualified naming convention: server.database.schema.object. Advantages of using schema:

  • Previously user was in the place of the schema, which either created problem since different users own different parts of schema and the change is not easy, or restricted the user to always be dbo.
  • Ability to group related objects. Previously objects had to be prefixed to identify different components of the more complex application. With schema objects in the same database can belong to different schemas.
  • Simplified permissions management: can give permissions on Schematic level, rather than on object level.

Fully qualified naming conventions are only used in certain cases (e.g. for linked servers). Server name can be omitted if connected to one server; db name can be omitted, if specified default db, and schema name can be omitted if specified default schema.

Creating a login

Right lick on Logins, choose New: create and manage Windows and SQL Server principals. Has the following tabs:

  • General Select user type and specify user/group name. Certain options (e.g. Enforce password policy/expiration, user must change password on next logon) are only available for SQL Server authentication. Also allows to specify default database. Enforce password policy (uses local policy of the machine on which it’s installed) is only available if installed on Windows 2003 or above. Enforce password expiration is only enabled if Enforce password policy is set. Also has advanced features of SQL Server authentication: Mapped to certificates/asymetric key.
  • Server roles Allows to give user permissions on server scope. Server roles can be created in Server Roles node, but fixed server roles are:
    public View databases
    bulkadmin Bulk insert operations
    dbcreator Create, alter, drop, backup, restore databases
    diskadmin Manage mdf, ldf files
    processadmin Stop/kill SQL processes
    securityadmin Create logins, grant/revoke/deny permissions, both server-level and database-level permissions.
    serveradmin Can change the properties of the server
    setupadmin Create/alter/drop linked servers
    sysadmin Everything in the system
  • User Mapping Create or map users on database level. Assign database role permissions.
  • Securables: when login is created on server level, this tab allows to configure explicit permissions for securables in server scope. Options:
    WITH GRANT (gives user or group the ability to grant this permission to someone else)
  • Status: grant/deny connection to DB engine, enable/disable login, lock/unlock SQL authentication user.

Note that Credentials allows SQL Server authenticated user to have presence outside of SQL Server, by associating SQL user with Windows user.

Assigning database roles and permissions

Schemas allow creating schemas and configuring permissions per schema. Each schema has ONE owner (e.g. dbo). Tables are created and associated with schemas (in table properties). Database users are created and mapped with SQL Server logins (database user might have the same or different name than server user). SQL Database user can have default schema (similar to how server user can have default database), but Windows users cannot have default schema.

When creating a database user:

  • General tab: specify user name, choose existing login name to associate with, choose default schema. Also allows to set which schemas user owns (but each schema has only one owner, so it replaces the owner). And allows to choose database roles.
  • Fixed database roles are:
    db_accessadmin Grant access to this database
    db_backupoperator Backup database
    db_datareader SELECT from database
    db_datawriter INSERT, UPDATE, DELETE from database
    db_ddladmin Data definition language administrator, can create any object: table, view, stored procedure, etc.
    db_denydatareader opposite of db_datareader
    db_denydatawriter Opposite of db_datawriter
    db_owner All permissions on the database
  • Securables tab: similar concept as for server login, but in this case securables are for the database. Thus there’ are more types of them.
  • Extended Properties tab: can create value/description pairs. For example for documentation purposes.

Add Securables to Database User

Can be done for:

  • Specific objects…
  • All objects of the types …
  • All objects belonging to schema…

For each object, permissions to select/insert/etc are provided. More granular permissions for columns can be set as well.

SQL Server 2005 security basics

Importing data from Excel to SQL Server

Different ways to accomplish this are described in Microsoft KB article #321686. If importing is not recurring, or persistent connection is not possible, it makes sense to use Distributed Query. Running such query in SQL Server Management Studio of SQL Server 2005 will, however, produce the following error:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

It can be resolved by enabling “advanced options” and “Ad Hoc distributed Queries” option, executing query to import data, and (optionally) reverting the configuration to its original state:

# First, enable advanced options
sp_configure ‘show advanced options’, 1
# Second, enable execution of Ad Hoc queries
sp_configure ‘Ad Hoc Distributed Queries’, 1
# Import data from Excel to SQL table
SELECT * INTO TableName FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=Location\Name.xls’, [SheetName$])
# Revert both options back: first disable Ad Hoc distributed queries
sp_configure ‘Ad Hoc Distributed Queries’, 0
# … And hide advanced options
sp_configure ‘show advanced options’, 0
Importing data from Excel to SQL Server

Script table contents as INSERT statements (SQL Server)

SQL Server Management Studio has nice ability to generate SQL Script for database schema, but has nothing for data. So if I want to preserve the entire table (schema and data) as SQL Script, in addition to generating “CREATE…” script, I’d need a set of INSERT statements to generate the static data in a table.
The best solution I’ve seen to date can be found as a comment to some other solution. The script goes like this:

declare @table_name nvarchar(100)
declare @columns    nvarchar(max)
declare @values     nvarchar(max)
declare @identity   bit
declare @sql        nvarchar(max)

set @table_name = 'MyTable'
set @columns    =''
set @values     =''
set @identity   = 0

@identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),
@columns  = @columns + ',' + '['+column_name+']',
@values   = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+'] 
as varbinary(max))),''NULL'')'
table_name = @table_name and data_type != 'timestamp'

set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ') 
values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name
if @identity=1 print 'set identity_insert [' + @table_name + '] on'
exec sp_executesql @sql
if @identity=1 print 'set identity_insert [' + @table_name + '] off'

Thanks to unknown hero who created it!

Script table contents as INSERT statements (SQL Server)

SQL Server: running some query on all databases

A little useful trick, lifted from somewhere:
sp_MSforeachdb 'use [?]
[additional statements come here]'
Any single quotes within the “additional statements” must be escaped with second single quote (not double quotes, as it may seem), e.g.:
sp_MSforeachdb 'use [?]
declare @tbl nvarchar(500)
select @tbl=name from sysobjects where type=''U'' and name=''MYTABLE''
if @res = ''MYTABLE''
begin	exec sp_spaceused

This procedure will retrieve size of the database if it finds a specific table (called MYTABLE) in it.

Basically the most valuable point here is: to use the name of the current database, question mark is used, e.g.:

sp_MSforeachdb 'DBCC CHECKDB(''?'')'

Another similar procedure is sp_MSforeachtable.

SQL Server: running some query on all databases