When Leaving Page, Prompt The User That Changes Have Not Been Saved

I’m in the process of create a few input forms in ASP.NET (Name, address, and etc).  If the user makes a change to the page and tries to navigate away from the current page without previously clicking save, the current page should prompt the user that there are changes to the page and does the user want to continue to navigate to a different page.  The prompt should be triggered by any of the following:  selecting a link, closing the window, entering a new URL in the address bar, selecting back, hitting refresh or other means. 
 
To solve this problem I first tried using onunload, but could not find any way of stopping the user from going to another page.

[sourcecode language=”xml”]
body onunload=”ConfirmExit”
[/sourcecode]

After a little research I found a method window.onbeforeunload.  window.onbeforeunload provides the developer a way to notify the user that there has been changes that have not been save before navigating to a different page.  In the following example I use ASP.NET instead of strait HTML.  The code behind loops through every webcontrol that’s in the form and wires up the control’s onchange JavaScript event to call InputChange function. If the user changes any of the controls the isDirty variable is set to true. 
 
As you can see in the following code the window.onbeforunload is set to ConfirmExit function. This is the part that is used to notifies the user. Any time the user tries to exit the page the ConfirmExit function will be called.  In the ConfirmExit function, if the isDirty variable is true, the user should be prompted with a message to confirm if they want to leave the page.  If isDirty is false the the user is redirect without a prompt.  
 
If the user clicks save button, I do no not want him to be prompted that the data has not been save. So I set the following for the button control.

[sourcecode language=”javascript”]
OnClientClick=”isDirty=false”
[/sourcecode]

 
I tested the following code and confirm it works in IE7 and FireFox 2.0

[sourcecode language=”csharp”]
public partial class TestPromptSave : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false) {
SetupJSdataChange(form1);
}
}

private void SetupJSdataChange(Control parentControl) {
foreach (Control control in parentControl.Controls) {
//Response.Write(control.ID + “<br>”);
if (control is WebControl) {
WebControl webControl = control as WebControl;
webControl.Attributes.Add(“onchange”, “InputChanged(this)”);
}
}
}
protected void Page_Save(object sender, EventArgs e) {
}
}
[/sourcecode]

HEre some more

[sourcecode language=”xml”]
<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”TestPromptSave.aspx.cs” Inherits=”TestPromptSave” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title>Untitled Page</title>
</head>
<body >
<script type=”text/javascript” language=”javascript”>
var isDirty = false;
window.onbeforeunload = ConfirmExit;

function InputChanged(control)
{
isDirty = true;
}
function ConfirmExit()
{
if(isDirty == true){
return “You have attempted to leave this page. If you have made any changes to the fields without clicking the Save button, your changes will be lost. Are you sure you want to exit this page?”;
}
}

</script>

<form id=”form1? runat=”server” >
<a href=”RedirectPage.aspx”>RedirectPage.aspx</a>
<br /><br />
<table>
<tr>
<td>
First Name
</td>
<td>
<asp:TextBox ID=”txtFirstname” runat=”server” />
</td>
</tr>
<tr>
<td>
Last Name
</td>
<td>
<asp:TextBox ID=”txtLastName” runat=”server” />
</td>
</tr>
<tr>
<td>
State
</td>
<td>
<asp:DropDownList ID=”ddlState” runat=”server” >
<asp:ListItem Text=”" Value=”" />
<asp:ListItem Text=”CO” Value=”CO” />
<asp:ListItem Text=”TX” Value=”TX” />
<asp:ListItem Text=”FL” Value=”FL” />
<asp:ListItem Text=”OK” Value=”OK” />
</asp:DropDownList>
</td>
</tr>
</table>
<br />
<asp:Button ID=”butSave” runat=”server” Text=”Save” OnClientClick=”isDirty=false” onclick=”Page_Save” /> &nbsp; | &nbsp;
<asp:Button ID=”butCancel” runat=”server” Text=”Cancel” />
</form>
</body>
</html>
[/sourcecode]

Resource:
MSDN – onbeforeunload Event
 
4 Guys From Rolla.Com – Prompting a User to Save When Leaving a Page
http://www.4guysfromrolla.com/webtech/100604-1.shtml
 
The IE OnBeforeUnload Event Handler
 
Reminding Users to Submit Forms
 
IE runs onbeforeunload twice for no reason?
 
Warn users about unsaved changes in a web form

SQL Server Searching Object Text

I had a situation where soft deletes were going to be implemented in the database, but for the past year hard deleted were being implemented.   On every table the IsDeleted column was created.  The newer Stored Procedures, Views, and Functions included IsDeleted in there WHERE statement, but all the db object that were created prior to implementing the IsDeleted needed to be changed to include the IsDeleted. 
 
The following was my first attempt to identify DB objects that did not include IsDeleted.  This seemed to return the correct data. But, there seem to be a problem.  There were situations where the results that were returned included duplicate names.  Come to find out the [Text] column that is returned from syscomments is NVARCHAR(4000).  If the [Text] of DB object exceeded 4000 characters an additional records was created every additional 4000 characters.   There were a few stored procedures that surpassed 4000 characters and in-turn had duplicate Object_names returned in the results .  This is not that big of a deal, but I wasn’t getting back the results I expected. 
 

SELECT OBJECT_NAME(id), *

FROM syscomments

WHERE

(

OBJECTPROPERTY(id, ‘IsScalarFunction’) = 1

OR OBJECTPROPERTY(id, ‘IsTableFunction’) = 1

OR OBJECTPROPERTY(id, ‘IsProcedure’) = 1

OR OBJECTPROPERTY(id, ‘IsView’) = 1

)

AND [Text] NOT LIKE ‘%IsDeleted%’

 
I found a solution to my problem by referencing sys.objects table and using the function OBJECT_DEFINITION, which returns the Transact-SQL source text of the definition of a specified object.  OBJECT_DEFINITION is not limited to NVARCHAR(4000), but instead is NVARCHAR(MAX).  I probably could have referenced INFORMATION_SCHEMA.Tables, but decided not to mess with it today and just wanted to get it to work.
 

SELECT

Name, *

FROM

sys.objects

WHERE

(

  OBJECTPROPERTY(object_id, ‘IsScalarFunction’) = 1

  OR OBJECTPROPERTY(object_id, ‘IsTableFunction’) = 1

  OR OBJECTPROPERTY(object_id, ‘IsProcedure’) = 1

  OR OBJECTPROPERTY(object_id, ‘IsView’) = 1

)

AND OBJECT_DEFINITION(object_id) NOT LIKE ‘%IsDeleted%’

 
 
 
References:
SQL Server OBJECT_DEFINITION (Transact-SQL)
 

SQL Server sysobjects

 

Searching Syscomments Accurately (View the Comments section)

 

SQL Server Custom Shortcuts

Querying the INFORMATION_SCHEMA  (But has info about SQL Server Shortcuts)
This is where I originally found info about SQL Server shortcuts.
 
Assigning shortcuts to commonly run tasks in SQL Server 2005 Management Studio
 
SELECT * FROM, sp_helptext ShortCut for SQL Query Analyzer
 
 
SQL SERVER – Query Analyzer Shortcuts (SQL Server default shortcut keys)
This is probably my favorite SQL Server web site

SQL Server For Each Procedure

I created my sp_ForEachProcedure base on Microsoft undocumented sp_msForEachTable.  It doesn’t have all the features of sp_msForEachTable, but does include a few of them.  I even added a few custom parameters (@print, @execute).   I did dynamic SQL because I was having problems creating the where statement.  I believe I could use a case, but went ahead and used the dynamic sql.  I would like to join to syscomments and search the actual stored procedure, but ran out of time. 
 
In the future I plan on creating sp_ForEachView
 
 
 

–example of sp_msforeachtable

–exec sp_msforeachtable @command1=’PRINT ”?”’, @whereand=’ and o.name like ”%Human%”’

 
ALTER

PROCEDURE sp_ForEachProcedure

  @command1

VARCHAR(MAX),

  @whereand

VARCHAR(MAX) = NULL,

  @print

BIT = 1,

  @execute

BIT = 0

AS

–DECLARE @command1 VARCHAR(MAX)
–DECLARE @whereand VARCHAR(MAX)
–SET @command1 = ‘PRINT ”?”’
–SET @whereand = ‘name like ”%contact%”’
 

DECLARE

@executeCommand NVARCHAR(MAX)

SET

@executeCommand =

  ‘ DECLARE @statementTable TABLE (ID INT IDENTITY(1,1) ,statement NVARCHAR(MAX)) ‘

+

  ‘ DECLARE @userCommand NVARCHAR(MAX) ‘

+

  ‘ INSERT INTO @statementTable (statement) ‘

+

  ‘ SELECT ‘

+

  ‘ REPLACE(”’

+ REPLACE(@command1, ””, ”””) + ”’, ”?”, ”[” + p.name + ”]”)’ + ‘ AS Statement’ +

  ‘ FROM SYS.Procedures p ‘

IF

(@whereand IS NOT NULL)

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ WHERE ‘

+ REPLACE(@whereand, , ””)

END

IF

(@print = 1)

BEGIN

  SET @executeCommand = @executeCommand + ‘ SELECT * FROM @statementTable’

END
IF

@execute = 1

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ DECLARE @loopCount INT ‘ +

  ‘ DECLARE @statement NVARCHAR(MAX) ‘ +

  ‘ SELECT @loopCount = COUNT(*)FROM @statementTable ‘ +

  ‘ WHILE @loopCount <> 0 ‘ +

  ‘ BEGIN ‘ +

  ‘ SET @statement = (SELECT statement FROM @statementTable WHERE ID = @loopCount) ‘ +

  –‘ PRINT ”EXECUTING: ” + @Statement ‘ +

  ‘ EXEC sp_executesql @Statement ‘ +

  ‘ SET @loopCount = @loopCount – 1 ‘ +

  ‘ END ‘

END

–FOR DEBUGGING
–PRINT @executeCommand

EXEC

sp_executesql @executeCommand

GO
 

–sp_ForEachProcedure ‘PRINT ”?”’, ‘NAME LIKE ”%Human%”’, 1
–sp_ForEachProcedure ‘GRANT EXECUTE ON ? TO Scrub ‘, ‘NAME LIKE ”%Human%”’, 1, 1
 
References:
Is a Temporary Table Really Necessary (How not to use cursors):

SQL Server Shrink and Truncating Database, Log and Indexes

I ran into problems compare two 8 gig plus database, but data wasn’t important.  I wanted to clear all the data from the database and use SQL Compare to show the difference. And make changes where needed.  There were situations where I needed to restore the database(s) and at 15 minutes it took pretty long.  I decided to clear all the data from the database and do a backup of the empty database and restore the empty databases instead.  This seems like a easy endeavor, but took me a while to figure it out. With that said,here’s how to Shrink and Trunate Database, Logs and Indexes
 
Very little of the following code is my own.  I used the resources at the end of the blog to create the following statement.
 
EXEC

sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

GO

EXEC

sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

GO

EXEC

sp_MSForEachTable

  ‘BEGIN TRY

  TRUNCATE TABLE ?
  END TRY
  BEGIN CATCH
  DELETE FROM ?
  END CATCH;’

GO

EXEC

sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

GO

EXEC

sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

GO

–DBCC LOGINFO

EXEC

sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 100)"

 

DBCC

SHRINKFILE(DataFileName, 1)— OR DBCC SHRINKDATABASE (DatabaseName, 1)

BACKUP

LOG DatabaseName WITH TRUNCATE_ONLY

DBCC

SHRINKFILE(DatabaseLogName, 1)

 
–INFO

DBCC

SQLPERF(logspace)

EXEC

sp_spaceused

DBCC

LogInfo

 
 
Simple example of sp_msForEachTable:

exec

sp_msforeachtable @command1=‘PRINT ”?”’, @whereand=‘ and o.name like ”%Loan_%”’

 
Resource:
SQL SERVER - Shrinking Truncate Log File - Log Full
 
Introduction to the Transaction Log
 

Great Article Why you want to be restrictive with shrink of database files
 
How to determine SQL Server database transaction log usage
 
Reducing SQL Server Index Fragmentation
 
How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable
 
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb (Great Article)

SQL – Using COALESCE to Build Comma-Delimited String

I had a query that returned one column with multiple records.  I wanted only one record with all the values to be comma separated.  I could do this with a cursor, but that throws a big warning sign.  So i decided to use the COALESCE function.  Below are some of the resources i used to solve my problem.
 
 

–**************************************************************************
–http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

DECLARE

@EmployeeList varchar(100)

SELECT

@EmployeeList = COALESCE(@EmployeeList + ‘, ‘, ) +

CAST(Emp_UniqueID AS varchar(5))

FROM

SalesCallsEmployees

WHERE

SalCal_UniqueID = 1

SELECT

@EmployeeList

–**************************************************************************
–http://www.sqlservercentral.com/scripts/Miscellaneous/31922/

create

procedure sp_return_students

as
set

nocount off

/* Declare variable which will store all student name */

Declare

@StudentName varchar(8000)

/* Query that will return student names and at the same time concatenate values. */

select

@StudentName = coalesce(@StudentName + ‘, ‘, ) + stu_name from tbl_students

/* At last, you just have to define column name that will store values */

Select

@StudentName As Student

How to find SQL Relationship and Dependencies (Dependency)

 
 
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
I tested the query at the following website and it seems to work.  In the response section someone claims to have modified the query and made it faster.  A where can be added to it easily to limit it to a specific table.
 
This is a cool system Stored Procedrue, but I believe it’s not documented.
EXEC sp_MSdependencies N’mo_company’, null, 1315327
 
System Stored Procedure that is documented, but doesn’t give you what you really want. But kinda cool.
EXEC sp_depends @objname = N’tablename’
 
*****************************************************************************************
EXEC sp_depends @objname = N’tablename’
 
EXEC sp_MSdependencies N’tablename’, null, 1315327
*****************************************************************************************
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
This is very long query. Optionally, we can limit the query to return results for one or more than one table.

SELECT
     K_Table  = FK.TABLE_NAME,
     FK_Column = CU.COLUMN_NAME,
     PK_Table  = PK.TABLE_NAME,
     PK_Column = PT.COLUMN_NAME,
     Constraint_Name = C.CONSTRAINT_NAME
FROM       INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN  (
     SELECT      i1.TABLE_NAME, i2.COLUMN_NAME
     FROM        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
         INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
             WHERE       i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
     ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
—- optional:
ORDER BY
     1,2,3,4
WHERE      PK.TABLE_NAME=’something’WHERE      FK.TABLE_NAME=’something’

WHERE      PK.TABLE_NAME IN (‘one_thing’, ‘another’)

WHERE      FK.TABLE_NAME IN (‘one_thing’, ‘another’)

*****************************************************************************************

SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME


SQL Server Scripts to Copy User Permission

I have the need to copy user permissions from one user to another.
 
I have two SQL Server database. The structure of both database are the same.  The first db is a source database with permission for set for a specific user (User1).  The second database has a new user (User2) with no permissions.  I want user2 have the same permission of user1.  The following links/scripts allowed me to copy user permissions.
 

/* ********************************************
******************************************** */

SET

NOCOUNT ON

DECLARE

@OldUser sysname, @NewUser sysname

SET

@OldUser = ‘User1

SET

@NewUser = ‘User2’

SELECT

‘USE’ + SPACE(1) + QUOTENAME(DB_NAME()) AS ‘–Database Context’

 

SELECT

‘–Cloning permissions from’ + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + ‘to’ + SPACE(1) + QUOTENAME(@NewUser) AS ‘–Comment’

 
SELECT

‘EXEC sp_addrolemember @rolename =’

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), ””) + ‘, @membername =’ + SPACE(1) + QUOTENAME(@NewUser, ””) AS ‘–Role Memberships’

FROM

sys.database_role_members AS rm

WHERE

USER_NAME(rm.member_principal_id) = @OldUser

ORDER

BY rm.role_principal_id ASC

 
SELECT

CASE WHEN perm.state <> ‘W’ THEN perm.state_desc ELSE ‘GRANT’ END

+ SPACE(1) + perm.permission_name + SPACE(1) + ‘ON ‘ + QUOTENAME(USER_NAME(obj.schema_id)) + ‘.’ + QUOTENAME(obj.name)

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ‘(‘ + QUOTENAME(cl.name) + ‘)’ END

+ SPACE(1) + ‘TO’ + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

+ CASE WHEN perm.state <> ‘W’ THEN SPACE(0) ELSE SPACE(1) + ‘WITH GRANT OPTION’ END AS ‘–Object Level Permissions’

FROM

sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE

usr.name = @OldUser

ORDER

BY perm.permission_name ASC, perm.state_desc ASC

 
SELECT

CASE WHEN perm.state <> ‘W’ THEN perm.state_desc ELSE ‘GRANT’ END

+ SPACE(1) + perm.permission_name + SPACE(1)

+ SPACE(1) + ‘TO’ + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

+ CASE WHEN perm.state <> ‘W’ THEN SPACE(0) ELSE SPACE(1) + ‘WITH GRANT OPTION’ END AS ‘–Database Level Permissions’

FROM

sys.database_permissions AS perm

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

WHERE

usr.name = @OldUser

AND

perm.major_id = 0

ORDER

BY perm.permission_name ASC, perm.state_desc ASC

 
 

/* ********************************************
******************************************** */

SELECT
CASE

protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘

WHEN 205 THEN ‘GRANT ‘

WHEN 206 THEN ‘REVOKE ‘

ELSE

‘DUMMY GRANT’ END

+

CASE action WHEN 26 THEN ‘REFERENCES’

WHEN 178 THEN ‘CREATE FUNCTION’

WHEN 193 THEN ‘SELECT’

WHEN 195 THEN ‘INSERT’

WHEN 196 THEN ‘DELETE’

WHEN 197 THEN ‘UPDATE’

WHEN 198 THEN ‘CREATE TABLE’

WHEN 203 THEN ‘CREATE DATABASE’

WHEN 207 THEN ‘CREATE VIEW’

WHEN 222 THEN ‘CREATE PROCEDURE’

WHEN 224 THEN ‘EXECUTE’

WHEN 228 THEN ‘BACKUP DATABASE’

WHEN 233 THEN ‘CREATE DEFAULT’

WHEN 235 THEN ‘BACKUP LOG’

WHEN 236 THEN ‘CREATE RULE’

ELSE ‘ DUMMY ‘ END + ‘ ON ‘

+

so.name + ‘ TO <Your Role/User on Target Server>’

from

sysprotects sp

inner

join sysobjects so on (so.id=sp.id)

inner

join sysusers s on (sp.uid=s.uid)

WHERE

s.[name] in (‘User1’)

 
 
 

/* ******************************************************
The following Script just provides a select of the previous script
****************************************************** */

SELECT
CASE protecttype WHEN 204 THEN ‘GRANT_W_GRANT ‘

WHEN 205 THEN ‘GRANT ‘

WHEN 206 THEN ‘REVOKE ‘

ELSE ‘DUMMY GRANT’

END

,CASE action WHEN 26 THEN ‘REFERENCES’

WHEN 178 THEN ‘CREATE FUNCTION’

WHEN 193 THEN ‘SELECT’

WHEN 195 THEN ‘INSERT’

WHEN 196 THEN ‘DELETE’

WHEN 197 THEN ‘UPDATE’

WHEN 198 THEN ‘CREATE TABLE’

WHEN 203 THEN ‘CREATE DATABASE’

WHEN 207 THEN ‘CREATE VIEW’

WHEN 222 THEN ‘CREATE PROCEDURE’

WHEN 224 THEN ‘EXECUTE’

WHEN 228 THEN ‘BACKUP DATABASE’

WHEN 233 THEN ‘CREATE DEFAULT’

WHEN 235 THEN ‘BACKUP LOG’

WHEN 236 THEN ‘CREATE RULE’

ELSE ‘ DUMMY ‘

END

,*

from

sysprotects sp

inner

join sysobjects so on (so.id=sp.id)

inner

join sysusers s on (sp.uid=s.uid)

WHERE

s.[name] in (‘User1’)