SQL Server Planet

SQL Server Express 2008 does not allow remote connections by default.  This is done by design as SQL Express is often used either as a development tool, or as a local installation for resident applications.  Another thing to note, is that SQL Express uses dynamic ports by default so in order to connect, The SQL [...]

One of my favorite aspects of schemas (which were introduced in 2005) is the ability easily manage permissions. For those getting caught in terminology, schemas are the new abstraction layer that replace object owners. In SQL 2000, every object had an owner. And that owner was a user. In most cases [...]

Roles can be created inside a database that encapsulate permissions. These permissions can be granular (as in denying read permissions to one table), or they can be global (as in being able to write to all tables).
Using the CREATE ROLE state we will walk through some typical examples.
First of all, we need to create [...]

The following script will add users to a database by first creating a logon for the Database Server then add user entries for each of the databases you want to give access to. Also specified are the role members for each database.
Add User Using Windows Authentication

– Create user windows Authentication
CREATE LOGIN [YourDomainName\JohnJacobs] FROM WINDOWS
WITH [...]

To add a user to a role, you must use the stored procedure sp_addrolemember.
First you must select the database that contains the user and the role.

USE YourDatabaseHere
GO
EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_accessadmin’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_backupoperator’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_ddladmin’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_denydatareader’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_denydatawriter’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_owner’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_securityadmin’, ‘JohnJacobs’

The example [...]

There are two methods to add a user in SQL Server. One way is to do so using code (programmatically), the second way is to use the interface. First we will explore the programmatic way, then we will walk through the interface.
First of all, there are two different ways users can login to [...]