Big Data Basics - Part 1 - Introduction to Big Data

Big Data has been a buzz word for quite some time now and it is catching popularity faster than pretty much anything else in the technology world. In this tip, let us understand what this buzz word is all about, what is its significance, why you should care about it, and more.

What is Big Data?

Wikipedia defines "Big Data" as a collection of data sets so large and complex that it becomes difficult to process using on-hand database management tools or traditional data processing applications.

In simple terms, "Big Data" consists of very large volumes of heterogeneous data that is being generated, often, at high speeds.  These data sets cannot be managed and processed using traditional data management tools and applications at hand.  Big Data requires the use of a new set of tools, applications and frameworks to process and manage the data.

Evolution of Data / Big Data

Data has always been around and there has always been a need for storage, processing, and management of data, since the beginning of human civilization and human societies. However, the amount and type of data captured, stored, processed, and managed depended then and even now on various factors including the necessity felt by humans, available tools/technologies for storage, processing, management, effort/cost, ability to gain insights into the data, make decisions, and so on.

Going back a few centuries, in the ancient days, humans used very primitive ways of capturing/storing data like carving on stones, metal sheets, wood, etc. Then with new inventions and advancements a few centuries in time, humans started capturing the data on paper, cloth, etc. As time progressed, the medium of capturing/storage/management became punching cards followed by magnetic drums, laser disks, floppy disks, magnetic tapes, and finally today we are storing data on various devices like USB Drives, Compact Discs, Hard Drives, etc.

In fact the curiosity to capture, store, and process the data has enabled human beings to pass on knowledge and research from one generation to the next, so that the next generation does not have to re-invent the wheel.

As we can clearly see from this trend, the capacity of data storage has been increasing exponentially, and today with the availability of the cloud infrastructure, potentially one can store unlimited amounts of data. Today Terabytes and Petabytes of data is being generated, captured, processed, stored, and managed.

Characteristics of Big Data - The Three V's of Big Data

When do we say we are dealing with Big Data? For some people 1TB might seem big, for others 10TB might be big, for others 100GB might be big, and something else for others. This term is qualitative and it cannot really be quantified. Hence we identify Big Data by a few characteristics which are specific to Big Data. These characteristics of Big Data are popularly known as Three V's of Big Data.

The three v's of Big Data are Volume, Velocity, and Variety as shown below.

Volume

Volume refers to the size of data that we are working with. With the advancement of technology and with the invention of social media, the amount of data is growing very rapidly.  This data is spread across different places, in different formats, in large volumes ranging from Gigabytes to Terabytes, Petabytes, and even more. Today, the data is not only generated by humans, but large amounts of data is being generated by machines and it surpasses human generated data. This size aspect of data is referred to as Volume in the Big Data world.

Velocity

Velocity refers to the speed at which the data is being generated. Different applications have different latency requirements and in today's competitive world, decision makers want the necessary data/information in the least amount of time as possible.  Generally, in near real time or real time in certain scenarios. In different fields and different areas of technology, we see data getting generated at different speeds. A few examples include trading/stock exchange data, tweets on Twitter, status updates/likes/shares on Facebook, and many others. This speed aspect of data generation is referred to as Velocity in the Big Data world.

Variety

Variety refers to the different formats in which the data is being generated/stored. Different applications generate/store the data in different formats. In today's world, there are large volumes of unstructured data being generated apart from the structured data getting generated in enterprises. Until the advancements in Big Data technologies, the industry didn't have any powerful and reliable tools/technologies which can work with such voluminous unstructured data that we see today. In today's world, organizations not only need to rely on the structured data from enterprise databases/warehouses, they are also forced to consume lots of data that is being generated both inside and outside of the enterprise like clickstream data, social media, etc. to stay competitive. Apart from the traditional flat files, spreadsheets, relational databases etc., we have a lot of unstructured data stored in the form of images, audio files, video files, web logs, censor data, and many others. This aspect of varied data formats is referred to as Variety in the Big Data world.

Sources of Big Data

Just like the data storage formats have evolved, the sources of data have also evolved and are ever expanding.  There is a need for storing the data into a wide variety of formats. With the evolution and advancement of technology, the amount of data that is being generated is ever increasing. Sources of Big Data can be broadly classified into six different categories as shown below.

Enterprise Data

There are large volumes of data in enterprises in different formats. Common formats include flat files, emails, Word documents, spreadsheets, presentations, HTML pages/documents, pdf documents, XMLs, legacy formats, etc. This data that is spread across the organization in different formats is referred to as Enterprise Data.

Transactional Data

Every enterprise has some kind of applications which involve performing different kinds of transactions like Web Applications, Mobile Applications, CRM Systems, and many more. To support the transactions in these applications, there are usually one or more relational databases as a backend infrastructure. This is mostly structured data and is referred to as Transactional Data.

Social Media

This is self-explanatory. There is a large amount of data getting generated on social networks like Twitter, Facebook, etc. The social networks usually involve mostly unstructured data formats which includes text, images, audio, videos, etc. This category of data source is referred to as Social Media.

Activity Generated

There is a large amount of data being generated by machines which surpasses the data volume generated by humans. These include data from medical devices, censor data, surveillance videos, satellites, cell phone towers, industrial machinery, and other data generated mostly by machines. These types of data are referred to as Activity Generateddata.

Public Data

This data includes data that is publicly available like data published by governments, research data published by research institutes, data from weather and meteorological departments, census data, Wikipedia, sample open source data feeds, and other data which is freely available to the public. This type of publicly accessible data is referred to as Public Data.

Archives

Organizations archive a lot of data which is either not required anymore or is very rarely required. In today's world, with hardware getting cheaper, no organization wants to discard any data, they want to capture and store as much data as possible. Other data that is archived includes scanned documents, scanned copies of agreements, records of ex-employees/completed projects, banking transactions older than the compliance regulations.  This type of data, which is less frequently accessed, is referred to as Archive Data.

Formats of Data

Data exists in multiple different formats and the data formats can be broadly classified into two categories - Structured Data and Unstructured Data.

Structured data refers to the data which has a pre-defined data model/schema/structure and is often either relational in nature or is closely resembling a relational model. Structured data can be easily managed and consumed using the traditional tools/techniques. Unstructured data on the other hand is the data which does not have a well-defined data model or does not fit well into the relational world.

Structured data includes data in the relational databases, data from CRM systems, XML files etc. Unstructured data includes flat files, spreadsheets, Word documents, emails, images, audio files, video files, feeds, PDF files, scanned documents, etc.

Big Data Statistics

• 100 Terabytes of data is uploaded to Facebook every day
• Facebook Stores, Processes, and Analyzes more than 30 Petabytes of user generated data
• Twitter generates 12 Terabytes of data every day
• LinkedIn processes and mines Petabytes of user data to power the "People You May Know" feature
• YouTube users upload 48 hours of new video content every minute of the day
• Decoding of the human genome used to take 10 years. Now it can be done in 7 days
• 500+ new websites are created every minute of the day

Source: Wikibon - A Comprehensive List of Big Data Statistics

In this tip we were introduced to Big Data, how it evolved, what are its primary characteristics, what are the sources of data, and a few statistics showing how large volumes of heterogeneous data is being generated at different speeds.

References

• http://en.m.wikipedia.org/wiki/Big_data
• http://strata.oreilly.com/2012/01/what-is-big-data.html

Next Steps

• Explore more about Big Data.  Do some of your own searches to see what you can find.
• Stay tuned for future tips in this series to learn more about the Big Data ecosystem.

Controlling Windows and SQL Server Services using Windows PowerShell

Windows services are programs that run in the background to perform specific functions. Many services start when the Windows operating system is booted to perform their operation. We normally manage Windows services by using the service management console, which can be launched by selecting Start > Run > and typing services.msc. Once you press enter a service management console will appear with a list of services on your server. This is the place from where you generally manage Windows services.

NOTE:-Always run PowerShell prompt in "Run As Administrator" mode for administrative tasks.

Getting a List of Windows Services

Get-Service: We can use this cmdlet to lists all Windows services on your local system or remote system.

--List all windows services on local machine.
Get-service


--list all windows services of a remote computer.
Get-Service -computername SERVERNAME

Filtering Windows or SQL Server services

We can apply filters as well to display a specific service. Suppose we want to see all services whose name begin with "SQL" or a specific service like MSSQLSERVER. We can use the -displayname parameter to display all services whose display name contains "SQL" And we will use -Name parameter to get a specific service like MSSQLSERVER.

--Display all services whose name contains SQL.
Get-service -displayname "*SQL*"


--Display a specific service.
Get-Service -Name MSSQLSERVER

We can also filter the output of these cmdlets. We can use another cmdlet where-object to filter any logs. Suppose we want to check all SQL server services which are in running state. We can add both cmdlets with the help of a pipe and filter as shown below. The output of the first cmdlet (before pipe) is saved in $ and we can use $ to access the output.

--Display all services whose status is in running state.
Get-service | Where-Object {$_.status -eq "Running"}


--Display all SQL Server services which are in running state.
Get-service -displayname *SQL* | Where-Object {$_.status -eq "Running"}


--Display all SQL Server Services which are in stopped state.
Get-service -displayname *SQL* | Where-Object {$_.status -eq "stopped"}

As we can see in the above screenshot, we can get all services which are in stopped state in the same way. I did not include a screenshot for the first set of code because the list was too large.

Starting and Stopping Services

To stop a service we use the Stop-Service cmdlet.

Suppose we want to stop one of the SQL Server services. First run get-service to list SQL services to check the status then stop it as shown below. If you want to shutdown all SQL Server services then we can also do this with the help of filters.

--Check the status of a SQL Server service.
Get-Service -Name SQLserveragent


--Stop above service.
Stop-Service -Name Sqlserveragent


--Stop all SQL Server services in one go.
Stop-Service -displayname *SQL* -force

Sometimes a few services will fail to stop by normal Stop-service cmdlet with the below error.

Stop-Service : Cannot stop service 'SQL Server (MANVENDRA) (MSSQL$MANVENDRA)' because it has dependent services. It can
 only be stopped if the Force flag is set.
At line:1 char:13
+ stop-service <<<<  -displayname *sql*
    + CategoryInfo          : InvalidOperation: (System.ServiceProcess.ServiceController:ServiceController) [Stop-Serv
   ice], ServiceCommandException
    + FullyQualifiedErrorId : ServiceHasDependentServices,Microsoft.PowerShell.Commands.StopServiceCommand
 

This means one or more services are dependent on this service, so to avoid this issue either we can stop all dependent services or we can use the -FORCE parameter along with Stop-Service as shown in the above screenshot.

Stopping services could create an issue, so to be safe we can add a few parameters to get a confirmation screen about what we will be performing.

Here we will add two parameters. The first is -whatif and second is -confirm. If you use -whatif it will display a preview about what you are going to do and if you are use -confirm it will ask you again to choose Y for yes and N for no to proceed. The below screenshot shows how both parameters work.

--Running cmdlet with -whatif parameter.
Stop-Service -displayname *SQL* -whatif


--Stop all SQL Server services which are in a running state with -confirm.
Get-service -displayname *SQL* | Where-object {$_.status -eq "running"} |Stop-Service -confirm

To start a service we use the Start-Service cmdlet.

Starting a service is the same as stopping a service. Just use Start-Service in place of Stop-Service and run with all parameters which we have used above.

Below are a few cmdlets which start a service.

--Start the service.
Start-service -Name SQLSERVERAGENT


--Check the status of the started service.
Get-Service -Name SQLSERVERAGENT


--Get a preview of Starting all SQL Server Services which is not started.
Get-Service -displayname *SQL* | where-object {$_.Status -eq "Stopped"} |start-service -whatif


--Starting all SQL Server Services which are not started.
Get-Service -displayname *SQL* | where-object {$_.Status -eq "Stopped"} |start-service -confirm


--Check the status.
Get-service -displayname *SQL*

I am not attaching screenshots since this is very similar to the stop-service cmdlet which were shown in the above screenshots.

We can also restart a services with the help of PowerShell cmdlets.

--Restart the services.
restart-service -name sqlserveragent


--Restart all SQL Server Services.
restart-service -displayname *SQL* -Force

Changing Configurations of Windows/SQL Server services

To change a configuration we can use Set-service.

This cmdlet will change the configuration of a service like startup types, display name, etc...

--Check the startup type of SQL Server Agent service.
 get-wmiobject win32_service | where {$_.Name -eq "sqlserveragent"}


--Change the startup type of SQL Server agent service to Automatic.
set-service sqlserveragent -startup automatic

You can again check whether the configuration is changed or not. See the below screenshot of the outputs for the above cmdlets.

Below are few more cmdlets which can be used to alter the configuration of any service. You can change the display name of a service, you can change the description of a service or you can start or stop a service with the set-servicecmdlet.

--Change the display name of SQL Server Agent service (use same method for any windows service).
set-service -name sqlserveragent -DisplayName "Microsoft Sql Server Agent"


--Change the description of SQL Server agent service.
set-service -name Sqlserveragent -description "SQL Server Agent Service to handle agent related tasks."


--You can also start or pause the services with the help of this cmdlet. 
--Suppose a SQL Server Agent service is stopped, we can start it by running.
set-service sqlserveragent -status running

Next Steps

• Try to use PowerShell cmdlets in your day to day activities. PowerShell is becoming more popular for SQL Server support on Windows core edition.
• Read more tips on PowerShell.

Tracking Login Password Changes in SQL Server

In a short time you can be up and running with collecting password change information using three different methods: server-side trace, event notifications, and SQL Server audit. Below I will provide an example using each technology. Note that all three examples are able to track password changes using ALTER LOGIN, the system procedure sp_password (deprecated since SQL Server 2005), or the Management Studio Login properties dialog.  

---

Server-Side Trace

Trace includes an event called "Audit Login Change Password Event" - which is much more reliable than capturing all batches and filtering on '%sp_password%' and '%ALTER%LOGIN%PASSWORD%'. The EventID is 107, so you can set up a very simple trace with the following code (make sure to set a proper path to the desired trace file):

DECLARE @TraceID INT, @MaxFileSize BIGINT;
SET @MaxFileSize = 5;


EXEC sp_trace_create 
    @TraceID OUTPUT, 
    2, 
    N'C:\Traces\PasswordChangeTrace', -- make sure to change this!
    @MaxFileSize,
    10; 


EXEC sp_trace_setevent @TraceID,107, 1,  1;
EXEC sp_trace_setevent @TraceID,107, 11, 1;
EXEC sp_trace_setevent @TraceID,107, 8,  1;
EXEC sp_trace_setevent @TraceID,107, 12, 1;
EXEC sp_trace_setevent @TraceID,107, 14, 1;
EXEC sp_trace_setevent @TraceID,107, 40, 1;
EXEC sp_trace_setevent @TraceID,107, 42, 1;


EXEC sp_trace_setstatus @TraceID, 1;


SELECT @TraceID;

Make note of the TraceID in the output. Once this has been running, you can use that TraceID to review the events that have been captured using the following query:

DECLARE @path NVARCHAR(255);


SELECT @path = [path]
FROM  sys.traces
WHERE id = <traceID from above>;


SELECT 
  LoginName  = TargetLoginName, 
  EventCount = COUNT(*), 
  FirstEvent = MIN(StartTime), 
  LastEvent  = MAX(StartTime)
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 107 -- in case you've added other events
GROUP BY TargetLoginName;

Since the above trace definition specifies a max of 10 x 5MB files, eventually an event that happens today will no longer be available through the above query. So as an added exercise you may consider periodically taking a snapshot of this data into a permanent table, and running your queries from there.  

---

Event Notifications

An alternative to trace is to set up a targeted Event Notification. These are lightweight, asynchronous messages sent via Service Broker that can be used to perform various actions in response to a specific event. One such event is AUDIT_LOGIN_CHANGE_PASSWORD_EVENT. In a lot of cases people use these to send an e-mail or start a job, but in this case we're just going to log to a table. We can create the following table in msdb:

USE [msdb];
GO


CREATE TABLE dbo.PasswordChangeLog
(
    LoginName  SYSNAME,
    EventTime  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

We will then need to set up a queue and a notification to handle our events:

CREATE QUEUE PasswordChangeQueue;
GO


CREATE SERVICE PasswordChangeService ON QUEUE PasswordChangeQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO


CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

And then the following procedure can be used to log events to our table:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @message_body XML;


    WHILE (1 = 1)
    BEGIN
       WAITFOR 
       ( 
         RECEIVE TOP(1) @message_body = message_body
         FROM dbo.PasswordChangeQueue
       ), TIMEOUT 1000;


       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChangeLog(LoginName) 
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
    END
END
GO

Finally, we can change the queue to call this stored procedure in response to the event:

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Now change the password for a few logins, and you should see results from the following query:

SELECT 
  LoginName, 
  EventCount = COUNT(*), 
  FirstEvent = MIN(EventTime), 
  LastEvent  = MAX(EventTime)
FROM dbo.PasswordChangeLog
GROUP BY LoginName;

---

Server Audit

The final option I'll present here is creating a Server Audit Specification. You may already be using Server Audit, and if so, handling password change auditing using this technology might make more sense than using either of the above two methods. (However note that Server Audit requires Enterprise Edition of SQL Server 2008 or SQL Server 2008 R2 - in SQL Server 2012, this feature has been made available in all editions.)

One of the options for a Server Audit Specification is LOGIN_CHANGE_PASSWORD_GROUP. We can set up a file-based audit to capture these events with the following code (note that this needs to be performed in master and you should update the file path appropriately - you probably don't want to rely on C:\ for this):

USE [master];
GO


CREATE SERVER AUDIT ChangePasswordAudit
  TO FILE (FILEPATH = 'C:\Audits\', MAXSIZE = 5MB, MAX_ROLLOVER_FILES = 10)
  WITH (ON_FAILURE = CONTINUE); -- important unless you want your server to halt on failure


ALTER SERVER AUDIT ChangePasswordAudit
  WITH (STATE = ON);


CREATE SERVER AUDIT SPECIFICATION ChangePasswordAuditSpecification
  FOR SERVER AUDIT ChangePasswordAudit
  ADD (LOGIN_CHANGE_PASSWORD_GROUP)
  WITH (STATE = ON);
GO

Once this is running, you can change a few passwords and then retrieve data from the audit using the following query:

DECLARE @folder VARCHAR(255);


SELECT @folder = log_file_path + '*' 
  FROM sys.server_file_audits 
  WHERE name = 'ChangePasswordAudit';


SELECT 
  LoginName  = target_server_principal_name, 
  EventCount = COUNT(*),
  FirstEvent = MIN(event_time), 
  LastEvent  = MAX(event_time)
FROM sys.fn_get_audit_file(@folder, DEFAULT, DEFAULT)
WHERE action_id IN ('PWR', 'PWC') -- PWR = ALTER LOGIN / SSMS, PWC = sp_password
GROUP BY target_server_principal_name;

As with the trace above, this file-based audit is limited to 10 x 5MB files. So you may want to change those options to have the audit data hang around longer, or you may consider occasionally storing the result of this query in a permanent table.

One important thing to note about Server Audit is that it records the event time in UTC, so you might notice that the timestamps are off depending on your time zone. Therefore you may need to look into adding a helper function that will convert any UTC date to your time zone. Since this can get complicated with Daylight Saving Time, I've often found it easier to just set up all of our servers to do everything in UTC. :-)  

---

Conclusion

As you can see, there are a variety of ways to set up tracking for password changes, and each method is relatively straightforward to implement. While it is still impossible to obtain this information from the past, once you have implemented one of the above solutions, you will be able to look back on this information over time.

Understanding How A User Gets Database Access in SQL Server

You're right, that if you look in SQL Server Management Studio you won't see a login's access to the master database unless the DBA has done something explicitly. An example is shown in Figure 1.

Figure 1:

But we do know that all logins can access the master database. So let's talk about how a login can connect to a given database. There are five possible ways a login can have permission to connect to a given database:

1. Explicit access is granted.
2. The login is a member of the sysadmin fixed server role.
3. The login has CONTROL SERVER permissions (SQL Server 2005/2008 only).
4. The login is the owner of the database.
5. The guest user is enabled on the database.

---

Explicit Access (Login Mapped to Database User):

The first way is if a login is given explicit access to a database. For instance, in SQL Server 2000, if I had a user MyTestUser, I would grant access like so from within the database:

EXEC sp_grantdbaccess 'MyTestUser'; GO  

In SQL Server 2005 and 2008 there are new T-SQL commands to create logins and users. So I would use the following command to do the same thing:

CREATE USER MyTestUser FOR LOGIN MyTestUser; GO  

A login granted access in this manner should appear in the sysusers table (SQL Server 2000) or the sys.database_principals catalog view (SQL Server 2005/2008). For instance, here's how I would match up users in a given database to their corresponding logins (SQL Server 2000):

SELECT sl.name AS 'Login', su.name AS 'User'  FROM master..syslogins sl
  JOIN sysusers su
    ON sl.sid = su.sid
ORDER BY sl.name, su.name;  

And here's how we'd do it in SQL Server 2005/2008:

SELECT sp.name AS 'Login', dp.name AS 'User' FROM sys.database_principals dp
  JOIN sys.server_principals sp
    ON dp.sid = sp.sid
ORDER BY sp.name, dp.name;

If you see a login match up to a user in this manner, then the login has access to the database.

---

Implicit Access (Member of Sysadmin Fixed Server Role):

All members of the sysadmin fixed server role map to the dbo user of every database. Therefore, if a login is a member of this role, it automatically has access to every database.

Here is the query for SQL 2000 to see members of the sysadmin fixed server role.

EXEC sp_helpsrvrolemember 'sysadmin'

Here is the query for SQL 2005/2008 to see members of the sysadmin fixed server role.

SELECT sp.name
FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp
     ON srm.member_principal_id = sp.principal_id
WHERE srm.role_principal_id = (
     SELECT principal_id
     FROM sys.server_principals
     WHERE [Name] = 'sysadmin')

---

Implicit Access (CONTROL SERVER permission - SQL Server 2005/2008):

The CONTROL SERVER permission gives equivalent rights as a member of the sysadmin role with a few exceptions, which aren't of importance here. Therefore, if a login doesn't map explicitly to a user in a database, but that login has CONTROL SERVER permissions, that login can still access the database. You can see who has CONTROL SERVER permissions by the following query:

SELECT sp.name 'Login'  FROM sys.server_principals sp
   JOIN sys.server_permissions perms
     ON sp.principal_id = perms.grantee_principal_id
WHERE perms.type = 'CL'     
  AND perms.state = 'G';

---

Implicit Access (Database Owner):

The database owner automatically maps into the database as the dbo user. The query given under explicit access should reveal the owner by just looking at the dbo user. However, another way is to query the sysdatabases table (SQL Server 2000) or sys.databases catalog view (SQL Server 2005/2008). Here's the SQL Server 2000 query that reveals all the owners of all the databases on the server:

SELECT db.name AS 'Database', sl.name AS 'Owner'  FROM sysdatabases db
  INNER JOIN syslogins sl
    ON db.sid = sl.sid
ORDER BY db.name;

And here's how to do the same thing in SQL Server 2005/2008:

SELECT db.name AS 'Database', sp.name AS 'Owner' FROM sys.databases db
  LEFT JOIN sys.server_principals sp
    ON db.owner_sid = sp.sid
ORDER BY db.name;

---

Implicit Access (Guest User Is Enabled):

The final way a login can get access to a database is if the guest user is enabled for that database. If a login cannot map in any other way, it'll use guest if that's available. That's actually how logins can access the master database. The guest user is enabled. With respect to user databases, the guest user should only be enabled in special cases. The default is for it to be disabled. However, there are two system databases which the guest user must always remain enabled. They are:

• master
• tempdb

And that explains why logins always have access to master, even when explicit rights aren't visible. To see if the guest user is enabled we can query sysusers (SQL Server 2000) or sys.database_permissions (SQL Server 2005/2008). Here's how to do it in SQL Server 2000:

SELECT su.name, CASE su.hasdbaccess WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Enabled' FROM sysusers su
WHERE su.name = 'guest';

In SQL Server 2005/2008 we have to look for the existence of the CONNECT permission at the database level for the guest user. If it exists, the guest user is enabled. If it doesn't, then the guest user is not.

SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled' FROM sys.database_principals dp
  LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions 
              WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
    ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest'; 

Understanding GRANT, DENY, and REVOKE in SQL Server

The first thing to understand about SQL Server security is that SQL Server is built with the idea that security has to be granted. In other words, if SQL Server sees no applicable permissions for a user, the user has no access. If you're familiar with Windows file or share permissions, it works the same way.

GRANT

In order for a user to be able to do something, he or she must be given permission to do it. We do this via the GRANT command. However, before we demonstrate that, let's do some setup of a test role and a test user in a test database I've created (aptly called TestDB):

Create Test User

USE TestDB;
GO 
CREATE ROLE TestRole;
GO 
CREATE USER TestUser WITHOUT LOGIN;
GO 
EXEC sp_addrolemember @rolename = 'TestRole', @membername = 'TestUser';
GO 

Create Tables and Permissions

Now let's create a schema, a couple of tables, and let's GRANT the ability to select against the first table.

CREATE SCHEMA Test;
GO 
CREATE TABLE Test.TestTable (TableID int);
GO 
GRANT SELECT ON OBJECT::Test.TestTable TO TestRole;
GO 
CREATE TABLE Test.TestTable2 (TableID int);
GO 

Test Harness Queries

Once that is done, let's use two "test harnesses" to test the user's ability to access the tables in question. Note that with the current permissions, the user should only be able to issue a SELECT against the first table.

-- Test Harness to verify how permissions work for Test.TestTable.
EXECUTE AS USER = 'TestUser';
GO 
SELECT * FROM Test.TestTable;
GO 
REVERT;
GO 
-- Test Harness to verify how permissions work for Test.TestTable2.
EXECUTE AS USER = 'TestUser';
GO 
-- This should fail initially, as there is no permission for this table
SELECT * FROM Test.TestTable2;
GO 
REVERT;
GO 

Seeing the Permissions

In order to see the permissions that are granted, we'll use the sys.database_permissions catalog view. If you issue this query now, you'll see the first GRANT we made. Re-use this query to see the permissions after each change:

-- Query sys.database_permissions to see applicable permissions
SELECT dp.class_desc, s.name AS 'Schema', o.name AS 'Object', dp.permission_name, 
       dp.state_desc, prin.[name] AS 'User'
FROM sys.database_permissions dp
  JOIN sys.database_principals prin
    ON dp.grantee_principal_id = prin.principal_id
  JOIN sys.objects o
    ON dp.major_id = o.object_id
  JOIN sys.schemas s
    ON o.schema_id = s.schema_id
WHERE LEFT(o.name, 9) = 'TestTable'
  AND dp.class_desc = 'OBJECT_OR_COLUMN'
UNION ALL
SELECT dp.class_desc, s.name AS 'Schema', '-----' AS 'Object', dp.permission_name, 
       dp.state_desc, prin.[name] AS 'User'
FROM sys.database_permissions dp
  JOIN sys.database_principals prin
    ON dp.grantee_principal_id = prin.principal_id
  JOIN sys.schemas s
    ON dp.major_id = s.schema_id
WHERE dp.class_desc = 'SCHEMA';

REVOKE

REVOKE undoes a permission, whether it's a GRANT or a DENY (more on DENY in a minute). If you issue the following REVOKE and then check the permissions, you'll note that the GRANT that was previously present for Test.Table1. After issuing the revoke command, re-run the test harness queries above against that table and you'll see that the user cannot query the table any longer.

-- Let's undo the permission using REVOKE;
REVOKE SELECT ON OBJECT::Test.TestTable FROM TestRole;

Remember, REVOKE doesn't cancel a GRANT. It doesn't block a GRANT. It removes a permission at the level specified to the security principal (user or role) specified. That's why we say it undoes a permission.

DENY

DENY blocks access. DENY trumps all other access. If a user has both a GRANT and a DENY on a given object, by whatever means, the DENY will take effect. For instance, let's consider the case of a GRANT SELECT against the Test schema. This would give the ability to issue a SELECT against any table or view in the Test schema. Try just applying this permission, re-checking the permission, and then testing the user's access to both Test.TestTable and Test.TestTable2. You'll see the user can now issue a SELECT query against both tables.

If you're not familiar with schemas and how they affect permissions, see this tip on nested permissions due to securables. Sometimes, if you look for an explicit permissions against a table or stored procedure, you won't see it. However, the user can execute the SELECT or EXECUTE respectively. If this is the case, then the permission is on a securable that contains the object. That's what we're doing here. The Test schema contains the TestTable and TestTable2 tables. So if a user has SELECT permissions against the Test schema, it also has SELECT permission against any tables and views within the Test schema.

-- Permission at the schema level
GRANT SELECT ON SCHEMA::Test TO TestRole;
GO 

Now let's apply a DENY. In this case I'm applying a DENY explicitly to the test user instead of going through a role. And I'm only applying it to the Test.TestTable object. Now re-run the test harness queries. You'll see the access is denied. However, you can still query Test.TestTable2. There isn't a DENY applied against it.

-- Specific DENY will block the GRANT 
DENY SELECT ON OBJECT::Test.TestTable TO TestUser;

And if you re-run the permissions script, you'll see all the permissions granted, to include the DENY.

Simple script to backup all SQL Server databases

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM.BAK

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

File Naming Format DBname_YYYYDDMM_HHMMSS.BAK

If you want to also include the time in the filename you can replace this line in the above script:

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

with this line:

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

Notes

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.

Next Steps

• Add this script to your toolbox
• Modify this script and make it a stored procedure to include one or many parameters
• Enhance the script to use additional BACKUP options

Automating Transaction Log Backups for All SQL Server Databases

With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you can not issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

DECLARE @name VARCHAR(50) -- database name   DECLARE @path VARCHAR(256) -- path for backup files   DECLARE @fileName VARCHAR(256) -- filename for backup   DECLARE @fileDate VARCHAR(20) -- used for file name  SET @path = 'C:\Backup\'   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)     + '_'     + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') DECLARE db_cursor CURSOR FOR   SELECT name  FROM master.dbo.sysdatabases  WHERE name NOT IN ('master','model','msdb','tempdb')     AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED') OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @name    WHILE @@FETCH_STATUS = 0    BEGIN           SET @fileName = @path + @name + '_' + @fileDate + '.TRN'          BACKUP LOG @name TO DISK = @fileName          FETCH NEXT FROM db_cursor INTO @name    END    CLOSE db_cursor    DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Next Steps

• Add this script to your toolbox
• Modify this script and make it a stored procedure to include one or many parameters
• Create a scheduled task to backup your transaction logs on a set schedule
• Take a look at this tip that does FULL backups for all databases.

Giving and removing permissions in SQL Server

SQL Server offers three pretty simple commands to give and remove access, these commands are:

• GRANT - gives a user permission to perform certain tasks on database objects
• DENY - denies any access to a user to perform certain tasks on database objects
• REVOKE - removes a grant or deny permission from a user on certain database objects

Here are some examples of these commands.

Allow users Joe and Mary to SELECT, INSERT and UPDATE data in table Customers

GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary

Revoke UPDATE access to table Customers for user Joe

REVOKE UPDATE ON Customers to Joe

DENY DELETE access to table Customers for user Joe and Mary

DENY DELETE ON Customers to Joe, Mary

As you can see from the above examples it is pretty easy to grant, deny and revoke access. In addition to grant SELECT, INSERT, DELETE and UPDATE rights you can also grant EXECUTE rights to run a stored procedure as follows:

GRANT EXEC ON uspInsertCustomers TO Joe

To determine what rights have been granted in a database use the sp_helprotect stored procedure.

In addition to granting rights to objects that you create you can also grant users permissions to do other tasks such as create tables, views, stored procedures, etc...  To grant a user permissions to create a table you would run this command.

GRANT CREATE TABLE TO Joe

As you can see granting rights and permissions to certain features is not all that difficult to do. Take the time to understand what permissions are really needed by the database users and grant, deny and revoke accordingly instead of just using the default database roles.

Next Steps

• Take a look at this tip on how to determine what permissions have been granted Auditing your SQL Server database and server permissions
• Take a look at these commands for other examples
  • GRANT
  • DENY
  • REVOKE
  • sp_helprotect

Giving and removing permissions in SQL Server

The simplest approach is to use the stored procedures that Microsoft has included with the database engine.  A lot of these same procedures are called when use the GUI, but instead of showing you data one object at a time you can take a look across your server or across your database. 

Here is a list of some of these useful commands, what they do and sample output from each command.

sp_helprotect	This command will show you the permissions that have been granted or denied for all objects in a database. You can also specify the object name to see the permissions for that just that object.
sp_helprole	This command will show you a list of all the database roles  You can also specify the role name to see information about just that role.
sp_helprolemember	This command will show you a list of all roles that have users in them as well as the user name.  You can also specify the role name to see the users for just that role.
sp_helpsrvrole	This command will show you a list of all the server roles.  You can also specify the server role if you only want to see info about just that one server role.
sp_helpsrvrolemember	This command shows you logins that have access to all server roles or you can specify just one server role to examine.
sp_helpdbfixedrole	This command shows a list of fixed database roles.  You can also specify just one role.
sp_helplogins	This command returns attributes about all of your logins or you can specify just one login.
sp_helpntgroup	This command shows you windows groups that have access to the current database.
sp_helpuser	This command shows you information about users that have access to the current database.

Next Steps

• Add these commands to the list of tools that you use to manage your SQL Server. These commands work for both SQL 2000 and SQL 2005
• Use these commands to take a periodic audit of your server and database permissions.

Database level permissions for SQL Server 2005 and 2008

SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

Below are the list of database-level permissions:

Permission	Effect
ALTER	This grants or denies the ability to alter the existing database.
ALTER ANY APPLICATION ROLE	This grants or denies the ability to create, drop, or alter application roles. The db_securityadmin fixed database role has this permission implicitly.
ALTER ANY ASSEMBLY	This grants or denies the ability to create, drop, or alter CLR assemblies. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY ASYMMETRIC KEY	This grants or denies the ability to create, drop, or alter asymmetric keys for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY CERTIFICATE	This grants or denies the ability to create, drop, or alter certificates for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY CONTRACT	This grants or denies the ability to create and drop contracts for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATABASE DDL TRIGGER	This grants or denies the ability to create, drop, or alter DDL triggers at the database level (not the server level). Thedb_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATABASE EVENT NOTIFICATION	This grants or denies the ability to create and drop database event notifications for service broker. The db_ddladminfixed database role has this permission implicitly.
ALTER ANY DATASPACE	This grants or denies the ability to create a partition schema within the database. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY FULLTEXT CATALOG	This grants or denies the ability to create, alter, or drop fulltext catalogs within the database. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY MESSAGE TYPE	This grants or denies the ability to create, alter, or drop message types for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY REMOTE SERVICE BINDING	This grants or denies the ability to create, alter, or drop remote service bindings for service broker. The db_ddladminfixed database role has this permission implicitly.
ALTER ANY ROLE	This grants or denies the ability to create or drop user-defined database roles. The db_securityadmin fixed database role has this permission implicitly.
ALTER ANY ROUTE	This grants or denies the ability to create, alter, or drop routes for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY SCHEMA	This grants or denies the ability to create, alter, or drop schema within the database. The db_accessadmin,db_ddladmin, and db_securityadmin fixed database roles have this permission implicitly.
ALTER ANY SERVICE	This grants or denies the ability to create or drop services for service broker. The user also must have REFERENCES permissions for all queues and contracts specified for the service. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY SYMMETRIC KEY	This grants or denies the ability to create, drop, or alter symmetric keys for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY USER	This grants or denies the ability to create, alter, or drop users within the database. The db_accessadmin fixed database role has this permission implicitly.
AUTHENTICATE	Grants or denies the ability to extend impersonation across databases even though explicit access isn't normally permitted.
BACKUP DATABASE	This grants or denies the ability to backup the database. The db_backupoperator fixed database role has this permission implicitly.
BACKUP LOG	This grants or denies the ability to backup the transaction log of the database. The db_backupoperator fixed database role has this permission implicitly.
CHECKPOINT	This grants or denies the ability to issue a CHECKPOINT statement against the database. The db_backupoperatorfixed database role has this permission implicitly.
CONNECT	This grants or denies the ability to enter the database. When a new user is created, it is granted by default.
CONNECT REPLICATION	This grants or denies the ability to connect to the database as a subscriber for the purpose of retrieving a publication via replication.
CONTROL	This grants the equivalent to ownership over the database. The db_owner fixed database role has this permission implicitly.
CREATE AGGREGATE	This grants or denies the ability to create a user-defined aggregate function defined by an assembly. The REFERENCES permission on the assembly must also be possessed.
CREATE ASSEMBLY	This grants or denies the ability to create or drop an assembly within a SQL Server database. If the assembly permission set requires EXTERNAL_ACCESS, the login must also have EXTERNAL ACCESS ASSEMBLY permissions. If the permission set requires UNSAFE, the login must be a member of the sysadmin fixed server role. Unlike ALTER ANY ASSEMBLY, the user must own or have CONTROL permissions on the assembly in order to drop it.
CREATE ASYMMETRIC KEY	This grants or denies the ability to create or drop an asymmetric key. Unlike ALTER ANY ASYMMETRIC KEY, the user must own or have CONTROL permissions on the asymmetric key in order to drop it.
CREATE CERTIFICATE	This grants or denies the ability to create or drop a certificate. Unlike ALTER ANY CERTIFICATE, the user must own or have CONTROL permissions on the certificate in order to drop it.
CREATE CONTRACT	This grants or denies the ability to create a contract for service broker. Unlike ALTER ANY CONTRACT, the user must own or have CONTROL permissions on the contract in order to drop it.
CREATE DATABASE DDL EVENT NOTIFICATION	This grants or denies the ability to create and drop database event notifications for service broker. Unlike ALTER ANY DATABASE DDL EVENT NOTIFICATION, the user must own the database DDL event notification in order to drop it.
CREATE DEFAULT	This grants or denies the ability to create a default. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE FULLTEXT CATALOG	This grants or denies the ability to create and drop fulltext catalogs within the database. Unlike ALTER ANY FULLTEXT CATALOG, the user must own the fulltext catalog in order to drop it.
CREATE FUNCTION	This grants or denies the ability to create a function. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE MESSAGE TYPE	This grants or denies the ability to create a message type for service broker. Unlike ALTER ANY MESSAGE TYPE, the user must own the message type in order to drop it.
CREATE PROCEDURE	This grants or denies the ability to create a stored procedure. This permission is granted implicitly to the db_ddladminand db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE QUEUE	This grants or denies the ability to create, alter, or drop a queue for service broker. The user must own the queue in order to drop it.
CREATE REMOTE SERVICE BINDING	This grants or denies the ability to create, alter, or drop remote service bindings for service broker. Unlike ALTER ANY REMOTE SERVICE BINDING, the user must own the remote service binding in order to drop it.
CREATE ROLE	This grants or denies the ability to create or drop user-defined database roles. Unlike ALTER ANY ROLE, the user must own or have CONTROL permission over the role to drop it.
CREATE ROUTE	This grants or denies the ability to create, alter, or drop routes for service broker. Unlike ALTER ANY ROUTE, the user must own the route in order to drop it.
CREATE RULE	This grants or denies the ability to create a rule. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE SCHEMA	This grants or denies the ability to create schema in the database. Unlike ALTER ANY SCHEMA, a user with this permission can only drop a schema it owns it or has CONTROL permission over it.
CREATE SERVICE	This grants or denies the ability to create or drop services for service broker. The user also must have REFERENCES permissions for all queues and contracts specified for the service. Unlike ALTER ANY SERVICE, the user must own the service in order to drop it.
CREATE SYMMETRIC KEY	This grants or denies the ability to create or drop a symmetric key. Unlike ALTER ANY SYMMETRIC KEY, the user must own or have CONTROL permissions on the symmetric key in order to drop it.
CREATE SYNONYM	This grants or denies the ability to create a synonym. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE TABLE	This grants or denies the ability to create a table. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE TYPE	This grants or denies the ability to create a type. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE VIEW	This grants or denies the ability to create a view. This permission is granted implicitly to the db_ddladmin anddb_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE XML SCHEMA COLLECTION	This grants or denies the ability to create an XML schema collection. This permission is granted implicitly to thedb_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
DELETE	This grants or denies the ability to issue the DELETE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
EXECUTE	This grants or denies the ability to issue the EXECUTE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
INSERT	This grants or denies the ability to issue the INSERT command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
REFERENCES	This grants or denies the ability to create relationships between objects such as foreign keys on tables referencing other tables or the use of SCHEMABINDING by views and functions. The permission is granted implicitly to thedb_ddladmin fixed database role.
SELECT	This grants or denies the ability to issue the SELECT command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
SHOWPLAN	This grants or denies the ability to see execution plans for queries executing within the database.
SUBSCRIBE QUERY NOTIFICATIONS	This grants or denies the ability to create a subscription to a query notification for when the results of a particular query would change.
TAKE OWNERSHIP	This grants or denies the ability to transfer ownership of an XML schema from one user to another.
UPDATE	This grants or denies the ability to issue the UPDATE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
VIEW DATABASE STATE	This grants or denies the ability to view conditions about the current database via the database-level dynamic management views or functions.
VIEW DEFINITION	this grants or denies the ability to view the underlying T-SQL or metadata on objects within the database. Thedb_securityadmin database fixed server role has this permission implicitly.

While all of these database-level permissions are important, some of the ones to pay particular attention to are:

• ALTER - can modify the database
• CONTROL - has ownership of the database. Permissions within the database are bypassed for this user
• ALTER ANY USER - can manage users within the database
• BACKUP DATABASE - can create a backup of the database
• BACKUP LOG - can create a backup of the log file.

Listing Permissions

A quick and easy script you can use to see what permissions are assigned at the database level is the following. It uses the sys.database_permissions catalog view along with sys.database_principals to tie to database users and roles:

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission] FROM [sys].[database_permissions] sec   JOIN [sys].[database_principals] prin     ON sec.[grantee_principal_id] = prin.[principal_id] WHERE sec.class = 0 ORDER BY [User], [Permission];

Granting Permissions

Granting rights is pretty straight forward.  To grant "ALTER" rights to user "DBUser1" you would issue the following command:

GRANT ALTER TO DBUser1