Click here to monitor SSC
  • Av rating:
  • Total votes: 132
  • Total comments: 29
Robyn Page

SQL Server Security Cribsheet

20 February 2007

Last updated: March 2014

SQL Server Security- The Crib Sheet

For things you need to know rather than the things you want to know

Contents

Introduction

In a production database, any access to data and processes must be restricted to just those people who require it. This is what is meant by ‘executing with minimum privileges’ and using the ‘least-privileged user account’.  The prime objectinve of database security is to control access to  data held in 'securables', individual database objects such as tables. However, if a security breach occurs, it aims to determine who did what, and when.

Each production database will have its own security policy set out, agreed, and documented. This follows on logically from the analysis of the value, sensitivity and nature of the data and processes within the application. It should be updated and available for inspection as part of any audit.  It defines all the access requirements of the different parts of the organisation.

SQL Server's security model is designed to give the flexibility to implement a number of different types of security policy, and allow for all the different application architectures currently in use. Nowadays, security includes encryption which will not be tackled in this cribsheet. Encryption can only provide defence in depth by limiting data loss even if access controls are bypassed, but does not control access.

Firstly, SQL Server must only have those features enabled that are absolutely necessary. This is easier to do with SQL Server 2005 onwards, but possible with all previous releases. One can cause havoc with such features as Web assistant, Ad-hoc remote queries, OLE Automation, xp_CmdShell, and xp_sendmail. It is always best to start with as many features turned off as possible, and configure the database for their use as, or when, you need them.

 By using Windows Groups, or by not sharing Windows or SQL Server Logins between users, the administrator or auditor can determine who did what within the system, at any point in time. A simple public-facing website may get its data from a database via one Login, whereas an application with a variety of sensitive, financial, or personal data will have a rich hierarchy of connection types. Ideally, each person who uses an application will have an associated Login. Ideally, this should be administered in one place, preferably the Active Directory. This is not always possible or practical.

Someone with a Login, which gives access to a Server, will need a username, or alias, in each database within that server that he needs to reach. He will, in effect, need to be registered as a user of a database. Furthermore, that user needs permission to access the various objects within the database, such as tables, procedures, views and so on, or to execute code that makes structural changes to the database. Typically, this is done by assigning him to a 'User Role', which then has the permissions assigned to it. As people come and go, their membership to the Role is assigned and revoked without having to fiddle with permissions.

A typical application will be used by a number of different Roles of users, the members of each Role having similar requirements; something like HR, Management-reporting, Dispatch, for example. Each Role will require different types of access to the database depending on their function in the organization.

Each Database Server can therefore manage its security at the server, database and schema level, using the 'permission hierarchy' as defined in ANSI SQL. The 'owner' of a particular database or schema has the power of controlling access within it via the 'Permission system'. Only the System Administrator can override this.

Overview

SQL Server Security has grown and developed in response to the changing architecture of applications, the demands of application developers, and the requirement for simplicity for network administration. SQL Server has tried to keep backward compatibility when it has made these changes, so the result can be slightly confusing on first inspection.

Originally SQL Server had its own simple login and password system, which was completely independent of Windows security, and was logically consistent. All groupings of users was done at database level, and there was just one privileged login to administer the system. This made the adding and removal of users from the network more complex as it required this to be done with the Logins on every server as well as at the NT Domain level. Integrated security was then introduced, with its concepts of domain users and domain groups, thereby solving some of the problems. There were now, however, groups defined at network level and others, now renamed 'roles', at database level. The Server-based administration rights were then assigned, as special roles, to Logins. And the database 'Owner' rights were reworked as 'fixed database roles' that could be reassigned to other database users. However, the old 'SA' login and 'DBO' user were kept for backward-compatibility. SQL Server 2005 has introduced more complexity, such as password policies and execution contexts, in order to tighten security. The most important introduction since 2005 has been the 'schema', which has taken on a hierarchical security role, when properly used, making the administration of permissions far easier.

Authentication-The Login system

Types of authentication

SQL Server authentication allows the DBA to maintain security and auditing control for the database servers independently of the system manager of the underlying operating system.

The downside of SQL Server's own security is that users have to remember their password for accessing the database and use this each time they connect. They have already supplied a password to log into their PCs. These two different passwords and logins may have different lifetimes and generation rules. Also, this type of security, when used for ODBC/ADO etc, always ends up with passwords being stored in unprotected places. Worse, the passwords are transmitted unencrypted over TCP/IP.

Only SQL Server logins can be used over simple TCP/IP. A connection must have a user name and password, which can be checked against entries in the syslogins table, (sys.Server_principals in 2005) otherwise it is terminated.

'Integrated security' can only be used if SQL Server is participating in the Windows Network. The advantages are password-encryption, password-aging, domain-wide accounts and windows administration. It is based on an "access token" which contains the user's unique security ID or sid, which is then used by the client to gain access to network resources such as SQL Server without having to supply login credentials again. If a user has an access token, then it means that he has previously passed authentication checks.

SQL Server can use Windows Security, or use both Windows Security and manage its own user logins. The chances are that, unless all access to the server is from within a domain, both will be required.

Logins

SQL Server will create some Logins automatically on installation (such as. SA), but most are subsequently created by the System administrator. A login ID is necessary for access to a database but not sufficient. It has to be granted access to the various resources on the server (Server instance in SQL Server 2005). It holds information that is relevant across databases, such as the user's default language.

Before someone with a Login ID (Except for the SA) can access a database he requires a username or role within the database, and that username/role must be granted statement permissions and Object permissions. This, traditionally, could only be granted or revoked by the SA or DBO (Database owner). In later versions of SQL Server, this can be done by anyone with the appropriate 'fixed server role', thereby allowing SA rights to be given to domain, or local, Groups of users. Every SQL Server login belongs to the public server role and when a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Fixed Server Roles

Logins can, where necessary, be assigned to one or more fixed server roles so that the SA can delegate some, or all, of the administration task. These roles are more relevant to automated processes than for adminstrative roles. These Server Roles are:

sysadmin
can perform any activity, and has complete control over all database functions.
serveradmin
can change server configuration parameters and shut down the server.
setupadmin
can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption.
securityadmin
can create and manage server logins and auditing, and read the error logs.
processadmin
can manage the processes running in SQL Server.
dbcreator
can create, alter, and resize databases.
diskadmin
can manage disk files.
bulkadmin
 can run the BULK INSERT statement.

One can therefore create logins using either domain or local users, and one can also create logins with Domain or local groups. You can also create logins with UserID/Password combinations for users who are not part of the Windows network. Any of these can be assigned all or some of the administration rights. On installation there will be :

  • A local administrators Group
  • A Local Administrator account
  • An SA Login
  • A Guest Login

The first three will have the SysAdmin role by default. The Guest login inherits the permissions of the ' Public' database role, and is used only where a login exists but has no access explicitly granted to the database. If you removed 'guest' from the master database, only the sa user could then log in to SQL Server! When users log in to SQL Server, they have access to the master database as the guest user.

Database Users

Database Users are database level security principals, not server logins. Logins are given access to a database user by associating a database user with a login ID. The User then refers to the login's identity in a particular database. Additionally, all users other than SA, can be associated with one or more Roles. When a database is created, a DBO (Database Owner) Role is automatically created, which has full privileges inside the database. However, one can create any number of 'user' roles. A special GUEST Role can be enabled if you want anyone who can log to the instance via a login ID to access a particular database. They will then do it via that 'guest' Role

Database Roles

A Database Role is a collection of database users. Instead of assigning access permissions to users, one can assign them to User-defined database Roles, a collection of users who have a common set of requirements for accessing the database: This saves a great deal of work and reduces the chance of errors.

If you are just using Integrated security, you can sometimes do without roles. This is because Logins can represent Windows Domain Groups. If the Domain Group fits the grouping of users you require in your database, then you can create a username for this group of users and manage the permissions for this user as if it was a role. In SQL Server 2012, a windows group  can be assigned a default schema, making security even simpler.

Upon creating a database, you should ensure that a server login id exists for everyone who will use the database. If possible, set their default database, in their login, to be your new database. You can also set their database user persona to default to a particular schema. Then you should, if necessary, create a number of Database Roles depending on the different classes of database access you will have. For each Login, (which can represent a group of users) you will need to create a Database User. Then you can assign each user to a Database Role. You can subsequently assign permissions to your Roles or Users according to your security plan.

As well as this user-defined Database role (or Group as it used to be called) there are fixed Database Roles, and the Public Database Role.

Fixed Database Roles

There are several fixed, pre-defined database roles that allow various aspects of the database administration to be assigned to users. Members of fixed database roles are given specific permissions within each database, specific to that database. Being a member of a fixed database role in one database has no effect on permissions in any other database. These roles are…

db_owner
allows the user to perform any activity in the database.
db_accessadmin
allows the user to add or remove Windows NT groups, users or SQL Server users in the database.
db_datareader
allows the user to view any data from all user tables in the database.
db_datawriter
allows the user to add, change, or delete data from all user tables in the database.
db_ddladmin
allows the user to make any data definition language commands in the database.
db_securityadmin
allows the user to manage statement and object permissions in the database.
db_backupoperator
allows the user to back up (but not restore) the database.
db_denydatareader
will deny permission to select data in the database.
db_denydatawriter
will deny permission to change data in the database.

To allow a user to add users to the database and manage roles and permissions, the user should be a member of both the db_accessadmin role and the db_securityadmin role.

Some of these roles are of a rather specialist nature. Of these database roles, possibly the most useful are the db_denydatareader and db_denydatawriter role If the application interface consists entirely of views and stored procedures, while maintaining ownership chains and completely avoiding dynamic SQL, then it is possible to assign the db_denydatareader and db_denydatawriter role for regular users to prevent their access to the base tables.

Public Database Role

This is created when a database is created. Every database user belongs to the Public Role. The public role contains the default access permissions for any user who can access the database. This database role cannot be dropped

Application roles.

Application roles are the SQL Server roles created to support the security needs of an application. They allow a user to relinquish his user permissions and take on an application role. However, they are not easy to use alongside connection pooling.

Authorisation:-The Permissions System

The database user has no inherent rights or permissions other than those given to the public role. All rights must be explicitly granted or assigned to the user, the user's roles, or the public role. The permission system determines which Roles or users can access or alter data or database objects. It determines what every Role or user can do within the database. The SA bypasses the permission system, and so has unrestricted access.

Most commonly, permissions are given to use a database object such as a table, or procedure. Such object permissions allow a user, role, or Windows NT user or group to perform actions against a particular object in a database. These permissions apply only to the specific object named when granting the permission and not to all the other objects contained in the database. Object permissions enable users to give individual user accounts the rights to run specific Transact-SQL statements on an object.

Permissions can be given or revoked for users and Roles. Permissions given directly to users take precedence over permissions assigned to Roles to which the user belongs. When creating a permission system, it is often best to set up the more general permissions first. Start with the 'public' Role first and then set up the other Roles, finally doing the overrides for individual users where necessary.

The permission system has a hierarchy of users for which permissions are automatically given.

SA

The SA account is actually a Login rather than a database user. The System Administrator is able to perform server-wide tasks. The System Administrator bypasses the entire permission system and can therefore repair any damage done to the permission system. It can also perform tasks that are not specific to a particular database

Only the System Administrator can create a device, Mirror a device, stop a process, shut down SQL Server, Reconfigure SQL Server, perform all DBCC operations or maintain extended stored procedures. Normally, only the SA creates or alters databases, though this permission can be delegated

DBO

A DBO has full permission to do anything inside a database that he owns. By default, the SA becomes the owner of a database that he creates, but ownership can be assigned. There can be only one DBO for each database. Other than the SA, only a DBO can restore a database and transaction log, delete a database, use DBCC commands, impersonate a database user, issue a checkpoint, grant or revoke statement permissions or alter a database. The DBO user has all the rights that members of the db_owner role have. The dbo is the only database user who can add a user to the db_owner fixed database role. In addition, if a user is the dbo, when he or she creates an object, the owner of the object will be dbo of that object, as one might expect. This is not true for members of the db_owner fixed database role. Unless they qualify their object names with the dbo owner name, the owner's name will be his or her username.

Normally, a db_owner role member could restore a database, but the information on who belongs to the db_owner role is stored within the database itself. If the database is damaged to the point where this information is lost, only the DBO can restore the database.

If a user is a member of the db_owner role but not the dbo, he can still be prevented from accessing parts of the database if DENY permissions have been set. This does not apply to the the dbo, because the dbo bypasses all permissions checks within the database.

Other DBO roles can be assigned to other users, such as creating objects and Backing up a database or transaction log

DBOO

By default, a user who creates an object is the owner of the object. Whoever creates a database object, the DBOO. Or Database Object Owner, is granted all permissions on that object.. Every other user is denied access until they are granted permissions. A user who creates a database object is the DBOO of that object. Members of the db_owner and db_ddladmin fixed database roles can create objects as themselves, with their usernames being given as owner, or can qualify the object name as being owned by the dbo.

Assigning permissions

If the database designer has been able to define an interface based on Stored Procedures, or views, then the permission system will be simple, requiring fewer permissions to be set. The Database administrator will have set up users and Roles and will be able to assign 'Execute' permission to just those procedures that are appropriate for that Role or user. As long as the tables accessed, updated or inserted-into by the stored procedure have the same ownership as the stored procedure, (unbroken ownership chain) then permission need not be assigned to the tables. A stored procedure can even update a system table as long as the creator of the stored procedure has the requisite permission when the procedure was created, and the database is configured to allow such a thing. Security can be further enhanced by denying all access by application users to the base tables with db_denydatareader and db_denydatawriter .

If the Database administrator is unfortunate enough to be associated with a database which requires direct access to tables or views, then permissions for 'Select', 'Insert;, ;Update' and 'delete' access will need to be assigned directly to the tables that hold your data. They will also entail using column-level permissions, which can overly complicate the security administration model.

If you ever need to grant permission on individual columns of a table, it is usually quicker to create a view, and grant permission on the view. This is carried forward to the individual columns of the tables that make up the view.

It is so unusual for 'Statement permissions' to be assigned that it need not be considered here. However, large development projects may involve the assignment and revoking of permissions to create database objects such as tables, views, procedures, functions, rules and defaults.

Object-level permissions can be to:

Select
-Select data from a table view or column
insert
-Insert new data into a table or view
Update
-Update existing data in a table view or column
Delete
-Delete rows from a table
Execute
-Execute a stored procedure, or a function
DRI
-allows references to tables that are not owned by the user to be set up directly without select permission
View Definition
- (SQL Server 2005 and later) Allows the viewing of the metadata.

from SQL Server 2005 onwards, there are also 'Send', 'Receive' and 'Take Ownership'  object-level permissions. For a schema,  the permissions in SQL Server 2012  are CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE,VIEW CHANGE, TRACKING and VIEW DEFINITION

Ownership chains

Sometimes, a developer will come up against the problem of 'ownership chains'. When a view or stored procedure is used, permissions are only checked for the contributing objects if there is a change of ownership somewhere along the chain. The most common time this happens is when 'Dynamic SQL' is executed by an Execute() or sp_executeSQL. And the user executing the procedure has no permission to access the objects involved. This is known as a Broken Ownership chain, because more than one user owns objects in a dependency chain.  Ownership chains can give unintended access, but it is possible to 'break' a chain by assigning a different owner to one of the links

User Context

When SQL Server is running, it needs a 'user context' in which to run. This is the user account that SQL Server uses to access resources on the machine and network. When SQL Server is installed, it is set up with the LocalSystem account, which cannot access the domain. This can be changed for a Domain account if required for backing up to a network disk, or for setting up replication. It is a good idea to use an account where the password is set to 'Password never expires'.. SQL Executive will need a domain account in order to publish data for replication.

Glossary

Object
A database object is either an Aggregate, Function, Procedure, Queue, Synonym, Table, or View
Principal
A principal is an individual, group, or process to which a permission to a securable can be assigned. They are either server principals or database principals. They come in three categories, Windows-level (Group, Domain Login, and Local login) SQL Server level (Fixed server role, SQL Server Login, User-defined fixed server role) and the database level (Fixed database role, database user, Application role or user-defined, or ‘flexible’, database role, User-mapped-to-certificate, and User-mapped-to-asymmetric-key) Windows-level, Database-level and Server-level principals all have different scopes of influence. They can also be categorized as to whether they represent an individual, a process or a collection of individuals. All principals except Database Roles must be owned by dbo.
Securable
A securable is any resource, either server-based (SQL Server Login, Endpoint or database) or database entity that can be secured with permissions using Permissions can be manipulated with Transact-SQL queries GRANT, DENY, and REVOKE. Database entities include Application role Assembly, Asymmetric key, Certificate, Contract, Full-text catalog, Message type, Remote service binding, Role, Route, Service, Symmetric key, User and Schema. Schema-based entities can be , Tables, views, Functions, Procedures, Queues, Synonyms, Types and XML schema collections.
Permissions hierarchy
Some securables can be contained within others, creating nested hierarchies called "scopes" that can themselves be secured. The securable scopes are server, database, and schema.
Database roles
These are actually groups of other principals that are defined at the database level. Every database has a collection of fixed database-level roles. In order to manage security more wasily, it is possible to create flexible database roles They are a powerful means of creating security but are also the cause of unintententionally allowing access to securables. (e.g. by adding a database role as a member of a fixed role). Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
Logins
The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. Beginning in SQL Server 2005, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server. Certificate-based SQL Server Logins. Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.
Default Schema
Only SQL Users, Windows users or Application roles can be mapped to default schemas.
Robyn Page

Author profile:


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs, when she was nominated as 'Most sexy newcomer' at the British Soap awards. She is currently having a career break to raise a young family.

Search for other articles by Robyn Page

Rate this article:   Avg rating: from a total of 132 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Waaaaay good !
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 20, 2007 at 9:51 PM
Message: Thanks, Robyn -
Incredibly useful information.

Subject: Where have you been all my life....
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 20, 2007 at 10:12 PM
Message: Robyn I cant tell you how many times I have needed something like this for a snap answer

Subject: A lifesaver
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 5:10 AM
Message: This is a useful reference - I feel like I don't need it now, but there'll come a time when I need a quick answer and will be hunting around for this. To avoid this, I've got a printout - hope I don't lose it. Thanks, Robyn.

Subject: Warn
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 5:36 AM
Message:

just a useful ref as help part.to the webmaster of site: limit comment by valid Email to gain access of word like first comments.

(Editor: The comments referred to in this post have been removed.)


Subject: Reference can be seen from SQL BOL
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 6:19 AM
Message: Impressive....

Subject: The Meat without fluff
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 6:30 AM
Message: We need a reference guide containing just this type of information. It takes forever to find information like this in moutains of SQL books!

Great Job!

Subject: Print Friendly?
Posted by: philm (view profile)
Posted on: Wednesday, February 21, 2007 at 8:41 AM
Message: Excellent summary. Maybe your web person could add an @media directive to the web page's css so that the header, footer and right column didn't need to be printed!! Good use of asp.net...makes it easy to use SQL server properly, doesn't it?!

Subject: RE: Print Friendly?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 8:59 AM
Message: There's a printer-friendly link at the top of the page.

Subject: Nicely done
Posted by: Rowland (view profile)
Posted on: Wednesday, February 21, 2007 at 9:23 AM
Message: I'll send some of my clients here to see this.

Thanks Robyn!

Rowland

Subject: Thank you!
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 11:28 AM
Message: This is a great article. I'll have to keep a link of it around so when I find myself explaining SQL basics, I can forward it to the person. Too many developers don't know enough about the underlying database they are using...

Subject: hmm
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 2:37 PM
Message: The article is ok (very basic... SQL 001), but regardless - the girl is smoking hot!


Subject: Re: Hmm.
Posted by: Robyn Page (view profile)
Posted on: Wednesday, February 21, 2007 at 3:01 PM
Message: Thanks. It is supposed to be a simple overview. I'm aiming to tackle more advanced issues in subsequent articles. I'd like to think that everybody working with SQL Server knows every part of what is in this article already.

I apologise to the experts. This is dedicated to the others!

Subject: Good intro to basics.
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 22, 2007 at 4:02 AM
Message: This was a comprehensive intro and revision for people who want to understand the security implementations in SS. I had liked your Backup Crib sheet too. Good work, Robyn

P.S.- Thank God Robyn that you chose to put up a 'real' picture of yourself and not a Victorian one like Phil. What would we have done!!

Subject: Real Picture? Ummmm....
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 22, 2007 at 8:34 AM
Message: Yes it is a real picture, but that's a headshot done by a professional photographer for a portfolio for her acting gigs. A "real" picture would be one you or I took of her (she'd still be hot).

BTW, once again, very useful SQL reference. Amazing how you go from SOAP actress to DBA....if ever there was a career 360, that's it.

Subject: Equine?
Posted by: Mr. Greene ATL (view profile)
Posted on: Thursday, February 22, 2007 at 10:21 AM
Message: I wish I had people saying how hot I was. I'm lonley now!

Subject: At a Glance...
Posted by: Anonymous (not signed in)
Posted on: Sunday, February 25, 2007 at 5:58 PM
Message: Oveall, I have surveyed all your articles and they do shine just a well as you do. I'm not use to seeing much beauty and brains in this industry - especially with relative upstarts. Very rare indeed. Keep targeting your articles in a tone that has mass appeal, regardless of the technical content, and you may just become famous (for more than a barmaid and soap actress). Oh, my 21 year year old calico cat Snowball (on my lap) says hi.

CQ
Santa Barbara, CA

Subject: Wonderful Robyn Page
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 28, 2007 at 5:00 AM
Message: It is really a great job. I wondered noticing that you are also a TV Star; naturally it is a rare combination. I was looking for a certain solution that is inserting a word like Girl’s or Boy’s through SQL statement. Can you help me in this issue?


E-mail: jewel041475@yahoo.com

Subject: Followup Workshop.?.
Posted by: JorgeLoco (view profile)
Posted on: Monday, September 17, 2007 at 5:11 PM
Message: Hello,

I was wondering if you still had an eta for the Security Workbench (showing how to manage security via TSQL) you mentioned?

Thank you.

Subject: DBO ALIAS
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 27, 2007 at 1:46 PM
Message: Nice article. You may want to mention about aliasing the dbo as well. When you add a user as a db_owner, the objects are created under that user. However, if you alias the user to dbo, then the objects are created as dbo

Subject: DBO ALIAS
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 27, 2007 at 1:47 PM
Message: Nice article. You may want to mention about aliasing the dbo as well. When you add a user as a db_owner, the objects are created under that user. However, if you alias the user to dbo, then the objects are created as dbo

Subject: What if this happens
Posted by: Anonymous (not signed in)
Posted on: Thursday, October 18, 2007 at 11:18 AM
Message: you only have default Admin(windows) and sa. Then you deny default admin the access. this leaves you with only 'sa' account. then you change authentication mode from mixed mode to windows. Now you can't login and you can't change the authentication mode. Can any body solve this?

Subject: New sql security management solution
Posted by: Mark (view profile)
Posted on: Friday, December 21, 2007 at 7:40 AM
Message: What an excellent guide! Great work, Robyn! Have you heard about new solution from scriptlogic for managing sql server security permissions ? They just released new version of<a href="http://www.scriptlogic.com/products/security-explorer/sql-server">security explorer</a> that supports sql. I implemented it some days ago and I want to mention that this tool can really simplify many operations that we did with scripting & queries and even can do some things that were unavialable with this and native tools.

Subject: Great!
Posted by: Anonymous (not signed in)
Posted on: Saturday, June 21, 2008 at 7:21 AM
Message: I did not read it properly. But this is the best way to write on articles that requires several books to finish. It a ocean in a bottle. Thanks !

Subject: Great!
Posted by: Anonymous (not signed in)
Posted on: Saturday, June 21, 2008 at 7:23 AM
Message: I did not read it properly. But this is the best way to write on articles that requires several books to finish. It a ocean in a bottle. Thanks !

Subject: Great!
Posted by: Anonymous (not signed in)
Posted on: Saturday, June 21, 2008 at 7:25 AM
Message: I did not read it properly. But this is the best way to write on articles that requires several books to finish. It a ocean in a bottle. Thanks !

Subject: Thanks!
Posted by: Anonymous (not signed in)
Posted on: Monday, July 07, 2008 at 8:16 AM
Message: Wish we had hot SQL Server DBA's like you working for us!!!

Subject: Good One
Posted by: Anonymous (not signed in)
Posted on: Thursday, July 17, 2008 at 8:12 AM
Message: Probably a rare combination of Brain and Beauty god has gifted...
Good one... keep it up....highly appreciated.

Subject: User defined roles
Posted by: Pete (view profile)
Posted on: Wednesday, August 13, 2008 at 10:45 AM
Message: Can you add your own roles in SQL Server 2005?

Thanks

kennedy.pete ... at .... gmail dot com

Subject: SQL Server Crib Sheet
Posted by: Black Bear (view profile)
Posted on: Tuesday, December 15, 2009 at 8:55 AM
Message: After a frustrating and fruitless search through the product documentation, 2 minutes spent reading this article told me exactly what I wanted to know.

Excellent - thank you.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.