Permissions To See Execution Plans

Hi Gurus,

What permissio0ns one should have to view execution plans on SQL SERVER 2005.


View Replies


Execution Plans &<&> Proportionate Execution Times

Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.

View Replies View Related

Execution Plans

HiCan you give me sone pointers to where I can get more information aboutthe various operations like index seeks,Bookmark Lookups,ClusteredIndex Scan in an execution plan.ThanksRagu

View Replies View Related

Execution Plans

I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:

rebuild the indexes
dbcc indexdefrag
update statistics

I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.

Any ideas would be greatly appreciated.



View Replies View Related

SQL Server Execution Plans

I'm looking for assistance on a problem with SQL Server. We have adatabase where a particular query returns about 3000 rows. This querytakes about 2 minutes on most machines, which is fine in thissituation. But on another machine (just one machine), it can run forover 30 minutes and not return. I ran it in Query Analyzer and it wasreturning about 70 rows every 45-90 seconds, which is completelyunacceptable.(I'm a developer, not a DBA, so bear with me here.)I ran an estimated execution plan for this database on each machine,and the "good" one contains lots of parallelism stuff, in particularthe third box in from the left. The "bad" one contains a "Nested Loop"at that position, and NO parallelism.We don't know exactly when this started happening, but we DO know thatsome security updates have been installed on this machine (it's at theclient location), and also SP1 for Office 2003.So it looks like parallelism has been turned off by one of these fixes.Where do we look for how to turn it back on? This is on SQL Server2000 SP3.Thanks for any help you might have for me!Christine Wolak -- SPL WorldGroup --Join Bytes!

View Replies View Related

Same Query - Different Execution Plans??

Hi,We are trying to solve a real puzzle. We have a stored procedure thatexhibits *drastically* different execution times depending on how itsexecuted.When run from QA, it can take as little as 3 seconds. When it iscalled from an Excel vba application, it can take up to 180 seconds.Although, at other times, it can take as little as 20 seconds fromExcel.Here's a little background. The 180 second response time *usually*occurs after a data load into a table that is referenced by the storedprocedure.A check of DBCC show_statistics shows that the statistics DO getupdated after a large amount of data is loaded into the table.*** So, my first question is, does the updated statistics force arecompile of the stored procedure?Next, we checked syscacheobjects to see what was going on with theexecution plan for this stored procedure. What I expected to see wasONE execution plan for the stored procedure.This is not the case at all. What is happening is that TWO separateCOMPILED PLANs are being created, depending on whether the sp is runfrom QA or from Excel.In addition, there are several EXECUTABLE PLANs that correspond to thetwo COMPILED PLANs. Depending on *where* the sp is run, the usecountincreases for the various EXECUTABLE PLANS.To me, this does not make any sense! Why are there *multiple* compileand executable plans for the SAME sp?One theory we have is, that we need to call the sp with the dboqualifier, ie) EXEC dbo.spHas anyone seen this? I just want to get to the bottom of this andfind out why sometimes the query takes 180 seconds and other timesonly takes 3 seconds!!Please help.Thanks much

View Replies View Related

Saving Execution Plans?

How can I save a graphical execution plan from Query Analyzer. I want to be able to email to someone else to evaluate.

I can save the text execution plans, but these don't contain the level of detail that the graphical ones do.

I can take screenshots but they don't capture the mouse over info and this requires a special screenshot tool as the plan is larger than the scrollable view.

Any ideas?

View Replies View Related

Estimated Execution Plans

I have a query that displays different execution plans on my production and development servers. If I look at the "estimated execution plan" through QA the same SQL statement is doing a multiprocessor access in development and single threaded processing in production.

Also the statement in production is spending 17% on a hash statement which it estimates will execute 4+10 whereas in development at the same junction it's not using any resources.

Where can I look to determine why the same statement is producing such significantly differing execution plans? What factors can contribute to this?

View Replies View Related

Printing Execution Plans

Does anyone have suggestions on how to print the QA Execution plan so that it is readable. If the query is anything more than a simple select statement, it gets shrunk down so small that it can't be read. It doesn't appear that you can cut and paste into another document. I just can't help but feel that I am missing something obvious...

Mike Sinnott

View Replies View Related

Parameter &&amp; Execution Plans.

Hi all,
I have a table TableA with few million rows. When I query TableA , the execution plans changes based on the input parameter as shown below . Why this happens ? How to resolve this ? Any inputs would be appreciated.
SELECT * FROM TableA WHERE Column1 = 1 => SELECT -> Clustered Index Scan (100%)
SELECT * FROM TableA WHERE Column1 = 2 => SELECT -> Clustered Index Scan (100%)
SELECT * FROM TableA WHERE Column1 = 3 => SELECT -> Parallelism (3%) -> Clustered Index Scan (97%)
SELECT * FROM TableA WHERE Column1 = 4 => SELECT -> Nested Loops -> Index Seek (50%) -> Clustered Index Seek (50%)
(takes a very long time to retrieve the records)
Thanks in advance,

View Replies View Related

Execution Plans - SQLCE 3

Does SQLCE 3 cache execution plans? Or even make use of them?



View Replies View Related

IO Stats And Execution Plans (text)

As a requirement, I have to send the DBA's IO Stats and Execution Plans (in text format) to them; with 2005, is there a more automated way?  Before, I tried using a script generator, but it really never did the trick.  Is there a better way of automating?

View Replies View Related

Different Execution Plans - Same Data, Same Server

Hi there - hoping someone can help me here!I have a database that has been underperforming on a number of queriesrecently - in a test environment they take only a few seconds, but onthe live data they take up to a minute or so to run. This is using thesame data.Every evening a copy of the live data is copied to a backup 'snapshot'database on the same server and also, on this copy the queries onlytake a second or so to run. (This is testing through the QueryAnalyser)I've studied the execution plans for the same query on the snapshot dband the live db and they seem to be significantly different - why isthis? it's looking at the same data and exactly the same code!!Anybody got any ideas???

View Replies View Related

How To Interprete Execution Plans For Queries

Pls tell me where i will be able to find a good material on interpreting the Execution do i compare 2 diff plans for Quries written in 2 diff same output

View Replies View Related

SQL Server: Execution Plans + Statistics

In using ADO to connect to SQL Server, I'm trying to retrieve multiple datasets AND statistics that are usually returned via the OnInfoMessage event. For those that are familiar with SQL Server, I need the results returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options. Anyone had any luck doing this before?

Thanks in advance.

View Replies View Related

Execution Plans Inconsistent With Performance

I've been working with SQL Server 2005 for a while now and I've noticed some odd behavior that I want to bounce of other members of the community. I should preface that I've been a forum viewer (and occasional contributer) here at SQL Team for a while and I've naturally developed a keen sense for optimizations.

Fundamentally, longer stored procedures with perfectly fine/optimized execution plans are inconsistent with real world performance. In some of these cases, a low subtree cost on a 4 core machine with 16gb of ram and 2 15 drive SAS arrays with little load takes excessively long to run or in some cases doesn't complete.

This isn't due to blocking or resource bottlenecks as I'm quite familiar with built in tools to troubleshoot and resolve those issues. In all cases, I am able to rearchitect the stored procedure into a higher subtree cost variant and get reasonable performance, but it's frustrating to have to redo work and there seems to be no common theme other than longer multi-statement procedures.

I've used SQL Server 2000 extensively and did not notice this level of inconsistency in performance with that product version. Just wondering if others in the community have experiences similar or if I'm just crazy.

Thanks for reading my rant.

- Shane

View Replies View Related

Bad Execution Plans In SQL Server 2005

Runnign Sql server 2005 standard edition SP2 on Win 2003 server x86_64, 8GB of RAM - 6.5 for the SQL server 2005
Compare to SQL server 2000 running on the identical box, 2005 has bad plans that are not influenced by rebuilding of the undexes and update statistics. Same data, same application/query, same indexes and tables.
All approrpiate indexes are on place and have updated stats. When running the Index Advisor, there are no recomendations for new indexes.
The main problem is that 2005 makes FTS on places that 2000 does not. The same queries that show FTS on 2005 are much slower then on 2000 and the users are complaining. I thought that if all indexes are good and there are good stats, I would eliminate the problem. But it doesn't look so.
Why SQL 2005 do not use perfectly fine indexes? If I try to put a hint, the situation gets even worse...

I need some advice on how to handle this issue.
Thanska  lot, mj

View Replies View Related

Invalid Execution Plans SQL Server 2000


I have a situation where there appears to be an invalid execution plan in cache for a stored procedure during busy periods. Estimated rows should equal actual rows approximately.

According to the BOL the plan can become bad because of the following reasons:

Certain changes in a database can cause an execution plan to be either inefficient or invalid, given the new state of the database. SQL Server detects the changes that invalidate an execution plan, and marks the plan as invalid. A new plan must then be recompiled for the next connection that executes the query. The conditions that cause a plan to be invalidated include:

Any structural changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).

New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.

Dropping an index used by the execution plan.

An explicit call to sp_recompile.

Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).

For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.

In our case none of the above is happening. The plan just for some reason becomes invalid. Microsoft is looking into this but I was hopeing someone on this board would have experienced the same type if issue.

Our work around so far has been to issue DBCC FREEPROCCACHE when the cpu starts to go up(Supposedly because of these bad plans).

This is a really high volume environment and a stored procedure may be hit in the 100,000s of times. For example at the peak today one was hit over 900,000 times.

If you have any ideas or experience with this please post here or email me directly.



Here are the details.

Bad plan/cardinality run during high CPU utilization
Rows EstimateRows StmtText
------ --------------- ---------------------------------------------
17 670.735 select distinct userid,[level],username from
17 670.735 |--Sort(DISTINCT ORDER BY:([Union1009] ASC, [
42 670.871 |--Concatenation
40 19.6799 |--Nested Loops(Inner Join, OUTER REFERENCES:
40 19.6799 | |--Filter(WHERE:([dvr].[OrderDept]=[dvr]
45 19.6799 | | |--Bookmark Lookup(BOOKMARK:([Bmk10
45 19.6799 | | |--Nested Loops(Inner Join, OU
13 13.9279 | | |--Clustered Index Seek(O
45 1.41299 | | |--Index Seek(OBJECT:([rl
40 1 | |--Nested Loops(Inner Join, OUTER REFERE
40 1 | |--Compute Scalar(DEFINE:([Expr1016
40 1 | | |--Constant Scan
40 1 | |--Clustered Index Seek(OBJECT:([rl
2 651.191 |--Hash Match(Inner Join, HASH:([ddd].[OrderD
66 71.9 |--Clustered Index Seek(OBJECT:([rl_util].[db
2 807.639 |--Compute Scalar(DEFINE:([Expr1012]=Convert(
2 807.639 |--Filter(WHERE:([did].[CountryCode]=[@countr
2 807.639 |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJE
2 807.639 |--Nested Loops(Inner Join, OUTER REFERENCES:
2 1026.26 |--Nested Loops(Inner Join, OUTER REFERENCES:
2 13.9279 | |--Clustered Index Seek(OBJECT:([appl_se
2 73.6838 | |--Index Seek(OBJECT:([appl_security_dat
2 1 |--Index Seek(OBJECT:([appl_data].[dbo].[div0

Baseline plan taken under normal operating conditions
Rows EstimateRows StmtText
------ -------------- ---------------------------------------------
17 16.656 select distinct userid,[level],username
17 16.656 |--Sort(ORDER BY:([Union1009] ASC))
17 16.656 |--Hash Match(Union)
45 11.2222 |--Hash Match(Inner Join, HASH:([dvr].[orderd
45 22.5168 | |--Hash Match(Inner Join, HASH:([va].[ve
10 14.606 | | |--Clustered Index Seek(OBJECT:([ap
18787 6429.8 | | |--Filter(WHERE:([dvr].[orderdept]=
20143 20281.9 | | |--Index Seek(OBJECT:([rl_util
80 83.8833 | |--Compute Scalar(DEFINE:([Expr1012]=Con
80 83.8833 | |--Clustered Index Seek(OBJECT:([rl
1 5.44687 |--Sort(DISTINCT ORDER BY:([ddd].[acctdeptnbr
2 5.44687 |--Nested Loops(Inner Join)
2 7.98062 |--Filter(WHERE:([did].[countrycode]=[@countr
2 7.98062 | |--Bookmark Lookup(BOOKMARK:([Bmk1006]),
2 7.98062 | |--Nested Loops(Inner Join)
2 8.03501 | |--Nested Loops(Inner Join)
2 1 | | |--Clustered Index Seek(O
2 8.03501 | | |--Index Seek(OBJECT:([ap
2 1 | |--Index Seek(OBJECT:([appl_da
2 1 |--Filter(WHERE:([ddd].[orderdeptnbr]=[ddd].[
2 1 |--Clustered Index Seek(OBJECT:([rl_util].[db

View Replies View Related

How To Reduce Number Of Query Execution Plans...

Hi everyone,

I've executed this script to navigate through a set of records based on the min value of recordid, and perform individual column updates on a user table which is only about 550 rows in size but I'd still like to know how I can reduce the number of execution plans to just one for the query analyzer?
So far, I get (1 row(s) affected) for every row that is updated and takes over 2 1/2 minutes to update this small table. The slowdown may be partially caused by the excessive paging that's going on my machine (we're trying to work that issue out).
Can anyone make a suggestion on optimizing this query?
DECLARE @lvID int, @lvFullName VARCHAR(50)

SET @lvId = (SELECT MIN(rowid) FROM irene)
--SET @lvFullName = (SELECT fullname FROM irene WHERE rowid = @lvId)

UPDATE irene
Set FirstName = Ltrim(Substring (@lvFullName, charindex(',', @lvFullName)+ 2, Len(@lvFullName))),
LastName = Ltrim(Substring (@lvFullName, 1, charindex(',',@lvFullName)-1))
Where rowid = @lvId
SET @lvId = (SELECT MIN(rowid)
FROM irene
WHERE rowid > @lvId)
SET @lvFullName = (SELECT FullName
FROM Irene
WHERE rowid = @lvId)

View Replies View Related

SQL Server 2000 Query Analyser Execution Plans

I have two sql statements in query analyser.




SELECT TOP 100 Percent FROM Member WHERE MemCode = 'ABCDEF'

The first statement users an Index Scan to find the data whilst the second one uses an index seek, which by definition is a lot faster than the first. The performance gains (I/O cost) is 10 to 50 times better. I haven't been able to find anything to support it. Has anyone come across this and more importantly is anyone aware of any problems using this method. e.g. table locking issues etc. And if not then why don't Microsoft use this as a default method for simple select statements??

View Replies View Related

Trigger Execution Permissions

Probably a simple question.

Do triggers execute using the permissions of the user that caused the trigger to execute?


View Replies View Related

DTEXEC &&amp; Package Execution Permissions

Using System.Diagnostics.Process, I am executing DTEXEC within a WCF Service hosted in IIS. The service is configured with a fixed identity via an application pool, so the context in which the process executes is that of the fixed identity.

When DTEXEC gets executed, unless the fixed identity executing it is a member of local admin group, I get a message in the console that states "Connect to SSIS Service on machine <machine name> failed: Access is Denied.

I haven't had any luck without adding the fixed identity to the local Admin group and this is not the best option for obvious reasons.

What group membership/permissions must this fixed identity be tied to on the SSIS server in order to succesfuly execute the package?

Note: I am familiar with proxy credentials, and understand that SQL Server Agent job is the best way to execute a package remotely but the Database Engine is not a system requirement for our product, so these are not an option.

Thanks in advance,


View Replies View Related

SSRS Permissions Error: Report Execution

I am trying remove a report's execution schedule and I get this error in the report manager website:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help Only members of sysadmin role are allowed to update or delete jobs owned by a different login.

This is the error I get in SSMS:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) (Report Services SOAP Proxy Source)

For help, click:


An internal error occurred on the report server. See the error log for more details. (rsInternalError) (ReportingServicesLibrary)

For help, click:


Only members of sysadmin role are allowed to update or delete jobs owned by a different login. () (.Net SqlClient Data Provider)



However I AM the systems administrator.

This is a dev server and I restored a backup from production to this server so I think that may be part of the issue.

However, I am unable to locate any details on which user account it thinks lacks these permissions. I am logged in as the administrator local to the machine and sys admin of the SQL database server.


EDIT: Could it be an issue with the Network Service account? I have that listed as the account in the IIS app pool that report server is using.

Since that was setup on my production server then moved to my dev server, could that be an issue as it is a LOCAL account not a domain account?

Maybe it doesn't have proper access?

View Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans


Anyone know why using

   ON =
instead of

    SELECT *
       ON =
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.

View Replies View Related

Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp

after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio).  If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected?  Is the fact that debugger doesnt show this behavior also expected? 

View Replies View Related

Execution Procedure Stored During Execution Of The Report .


Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
 Thnak you.

View Replies View Related

Table Permissions Versus View Permissions

Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column?  I have a password field in a database in which I do not want anyone to have select permissions on (except one user).  I denied access in the table itself, however the views still allow for the user to select that password.  I know I can go through and set this on a view by view basis, but I am looking for something a little more global.

View Replies View Related

Execution In Scheduled Job Vs Direct Execution

Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table

If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.

I used the same record, same parameters, and the same statements to call the stored procedure.

Any idea?

View Replies View Related

User Permissions Vs. Role Permissions

Which takes precedence?

User Tom has Exec permissions on MyStoredProc. He is also a member of the Public role which does not specifically state that he has Exec permission on MyStoredProc. However it also does not implicitly 'deny' that permission. Is there a "cumulative permission" effect here? Or is the Public role being 'more' restrictive and therefore not allowing Tom to Exec MyStoredProc?

From BOL.
"A user account can be a member of any number of roles within the same database. For example, a SQL Server user can be a member of the admin role and the users role for the same database, with each role granting different permissions. The effective permissions on an object granted to a member of more than one role are the cumulative permissions of the roles, although a denied permission in one role has precedence over the same permission granted in another role. For example, the admin role may grant access to a table, whereas the users role denies access to the same table. A member of both roles is denied access to the table because denied access is the most restrictive."

If I grant Exec permission on the MyStoredProc to the Public role, then Tom can execute the procedure.

I am all confused.


View Replies View Related

Copyrights 2005-15, All rights reserved