More Question on Permissions

I like to thanks all of you helps.
but I still can't find information I need among
sysprotects, sp_helpprotect,
INFORMATION_SCHEMA.table_priviledges, and
INFORMATION_SCHEMA.column_priviledges.
What I need to know is if a user don't have certain
priviledge, was it been revoked or denied?
For example, if a user has no UPDATE privildge on a
table's one column, was it been denied or revoked(never
been granted)?
Greg Chang
hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2276c01c45d2c$35de6cd0$a501280a@.phx.gbl...
> I like to thanks all of you helps.
> but I still can't find information I need among
> sysprotects, sp_helpprotect,
> INFORMATION_SCHEMA.table_priviledges, and
> INFORMATION_SCHEMA.column_priviledges.
> What I need to know is if a user don't have certain
> priviledge, was it been revoked or denied?
> For example, if a user has no UPDATE privildge on a
> table's one column, was it been denied or revoked(never
> been granted)?
it all depends on user database role membership too...
SET NOCOUNT ON
CREATE DATABASE GREG
GO
USE GREG
GO
EXEC sp_adduser @.loginame = 'roby'
, @.name_in_db = 'roby'
GO
CREATE TABLE dbo.Protected (
Id INT NOT NULL ,
Data VARCHAR( 10 )
)
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Id]) TO [roby]
DENY INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Data]) TO [roby]
GO
EXEC sp_helprotect @.name = [Protected]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
REVOKE ALL ON [dbo].[Protected] TO [roby]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
GO
USE master
GO
DROP DATABASE GREG
--<---
Granted database access to 'roby'.
Owner Object Grantee Grantor ProtectType Action Column
-- -- -- -- -- -- --
dbo Protected roby dbo Deny Delete .
dbo Protected roby dbo Deny Insert .
dbo Protected roby dbo Deny Update Data
dbo Protected roby dbo Grant Select Id
dbo Protected roby dbo Grant Update Id
(5 row(s) affected)
Server: Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'Data' of object 'Protected', database 'G
REG', owner 'dbo'.
Id
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
as you can see, user "roby" does not belong to particular database role, so
it has no explicit access to base table [dbo].[Protected]... he has benn
granted SELECT, INSERT, DELETE, UPDATE permission only on
[dbo].[Protected].[Id], while explicit DENY has ben set on
[dbo].[Protected].[Data] column...
and even after revoking all privleges, he still has no access to the base
table...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2276c01c45d2c$35de6cd0$a501280a@.phx.gbl...
> I like to thanks all of you helps.
> but I still can't find information I need among
> sysprotects, sp_helpprotect,
> INFORMATION_SCHEMA.table_priviledges, and
> INFORMATION_SCHEMA.column_priviledges.
> What I need to know is if a user don't have certain
> priviledge, was it been revoked or denied?
> For example, if a user has no UPDATE privildge on a
> table's one column, was it been denied or revoked(never
> been granted)?
it all depends on user database role membership too...
SET NOCOUNT ON
CREATE DATABASE GREG
GO
USE GREG
GO
EXEC sp_adduser @.loginame = 'roby'
, @.name_in_db = 'roby'
GO
CREATE TABLE dbo.Protected (
Id INT NOT NULL ,
Data VARCHAR( 10 )
)
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Id]) TO [roby]
DENY INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Data]) TO [roby]
GO
EXEC sp_helprotect @.name = [Protected]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
REVOKE ALL ON [dbo].[Protected] TO [roby]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
GO
USE master
GO
DROP DATABASE GREG
--<---
Granted database access to 'roby'.
Owner Object Grantee Grantor ProtectType Action Column
-- -- -- -- -- -- --
dbo Protected roby dbo Deny Delete .
dbo Protected roby dbo Deny Insert .
dbo Protected roby dbo Deny Update Data
dbo Protected roby dbo Grant Select Id
dbo Protected roby dbo Grant Update Id
(5 row(s) affected)
Server: Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'Data' of object 'Protected', database 'G
REG', owner 'dbo'.
Id
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
as you can see, user "roby" does not belong to particular database role, so
it has no explicit access to base table [dbo].[Protected]... he has benn
granted SELECT, INSERT, DELETE, UPDATE permission only on
[dbo].[Protected].[Id], while explicit DENY has ben set on
[dbo].[Protected].[Data] column...
and even after revoking all privleges, he still has no access to the base
table...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

More problems with Gridview and SQL Procedures

I have successfully used SQL procedures to provide data to a Gridview previously, and I just can't see what's different about the simplified case that works and the real case that doesn't. I've extracted the code into a test page to avoid extraneous bumph.

1. I have developed a SQL procedure, GDB_P_Children, and tested this in SQL Server Management Studio Express. It returned exactly what it should. The procedure is: -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier,
@.Option int,
@.Owner Varchar(50),
@.User Varchar(50)
AS
Select * from GDBChildren(@.Indiid, @.Option, @.Owner, @.User)
GO

2. I then tried to use it in my program to power a Gridview, but the gridview was blank. I put the Gridview into a test page that only had code: -

Option Compare Text
Partial Class test
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Session("INDIid") = "ffe1fb2f-88ce-4b64-a358-e21efd161d70"
Label2.Text = Session("INDIid") ' These are just for debugging
Session("Owner") = "robertb"
Label3.Text = Session("Owner") & ","
Session("Option") = 0
Label4.Text = Session("Option") & ","
Session("gdvChildUser") = ""
Label5.Text = Session("gdvChildUser") & ","
End Sub
End Class

I then regenerated the gridview from scratch: -

Toolbox: drag a gridview on to the page
Click "Configure Data sourec" => New Data Source => SQL Database. Name the datasource SQLChildren.
Connection string - select as normal
Configure: Specify a custom SQL statement or procedure
Select Stored Procedure, select GDB_P_Children
Set parameters to: -
INDIid Session("INDIid")
Option Session("Option")
Owner Session("Owner")
User Session("gdbChildUser")

Test Query showed that the first parameter, INDIid, had type Object, and I know that this will fail (seehttp://forums.asp.net/thread/1390374.aspx), so I finished data source configuration and edit the source of the test page to remove Type="Object" from the parameter definition. The parameter now has type "Empty", and filling it in with the values ffe1fb2f-88ce-4b64-a358-e21efd161d70, 0, robertb, and '' returns data as expected. Click Finish and the Gridview configures itself with the correct column headings: -

Exactly correct so far. Yet executing the page shows only my debugging labels, with no sign of the gridview.

I changed the stored procedure: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Temporary testing version, stripped to bare essentials
ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier
AS
Select INDIid, Indiusername, dbo.gdbindinamedate(INDIid) as IndiNameDate, Indisex
from nzgdb_Indi where INDIMothersindiid = @.INDIid

Data configuration was redone, then the page was displayed. It now correclty shows data from the database. So what's different?

I changed the procedure back to the original, and again the blank (except for debugging labels) page was displayed.

The data configuration was changed so that only the first parameter, @.INDIid, was passed, and the other were set to default values. Still the blank page.

Procedure GDB_P_Children was then changed to have only one parameter, with the other three declared and given values internally: -

ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier
AS
Declare @.Option int
Declare @.Owner Varchar(50)
Declare @.User Varchar(50)
Set @.Option = 0
Set @.Owner = 'robertb'
set @.User =''
Select * from GDBChildren(@.Indiid, @.Option, @.Owner, @.User)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

The page is now OK, with the gridview showing data as expected.

So why can't I use this procedure with the four arguments that it is supposed to have? This is driving me crazy! I have other procedures with 2 arguments (both Uniqueidentifier) that work fine, so it's not the fact that this procedure has more than one argument that's causing problems. Help!!!!

Regards,

Robert Barnes

It look like a parameter issue to me. Can you open SQL profile and run a trace to see what is the parameter passed to the sp? normally that will tell you what is wrong

Hope this help

|||

My guess...

User you are setting to an empty string. The user parameter is set up to convert empty strings to a null. You also have the sqldatasource set up to cancel selects when one or more of the parameters is a null value.

|||Motely, you're right. The program worked fine when User is not null, and then when I changed the last parameter (User) to "Convert Empty String:False" it worked fine whether it was empty or not. Thank you.|||

David, I am having trouble running SQL profile. It won't recognise the SQL 2000 server on my laptop, even though it's started from Enterprise Manager which is running perfectly. In the meantime, Motely has solved my problem for me. Thank you for your help.

|||What's the meaning of "It won't recognise the SQL 2000 server on my laptop"? Did you mean SQL Profiler can not connect to the SQL 2000 instance by using the instance name? Then make sure you've enabled both TCP/IP and Named Pipes are enabled on the SQL instance as well as client.|||

"It won't recognize the SQL 2000 server on my laptop" means that: -

1. I have SQL 2000 running smoothly, using Windows Authentication. I can see it with either (or both) Enterprise Manager and with MS SQL Server Management Studio Express, and I can do any of the usual functions such as opening tables, running queries, ... The database is functioning normally with my application code.

2. I open SQL profiler (all programs/MS SQL Server/SQL Profiler) and click New Trace, which opens a dialog "Connect to SQL Server". Clicking the combo shows blank. Clicking the [...] opens another dialog, "Select server", but there is nothing in the list of active servers (there should be my laptop server, plus another). Going back to "Connect to SQL Server", I try typing the name of the server into the combo: the system thinks for a few seconds (30? I didn't time it) and then responds "SQL Server does not exist or access denied"

3. Using Enterprise Manager, I right-clicked my laptop server, clicked properties, on the general tab clicked Network Properties. TCP/IP and Named Pipes are already enabled.

So why won't SQL Profiler see the server?

|||

If you're using Profiler to connect to remote SQL2000 instance, and SQL2005/Express exists on the remote server, make sure the SQL Brower Service on the remote server is started. If the connection still fails, you can take a look at this post to get an idea of checking remote database connection:

http://forums.asp.net/thread/1289341.aspx

|||Did this thread get mixed up - this seems to be part of another conversation. My SQL Server 2000, VWD2005, etc are ALL running locally on my laptop. Presumably my inablity to run SQL Profiler is not a network issue.|||

Of course we can disscuss this in a new post Then run 'cliconfg' to make sure you've enabled Shared Memory connectivity

|||

I ran cliconfg, and shared memory connectivity was already on: -

Click start button => click Run => Run dialog box. Enter cliconfg, click OK => SQL Server Client Network Connectivity. On General tab, towards the bottom, there is a check box "Enable Shared Memory Protocol". This is already checked.

What next?

Regards, Robert

|||There two other places you may be able to access Profiler connect to Query Analyzer and at the top you will see tools you should see the profiler there, another place is through the wizards most people don't know they exist because they are context sensitive so open your database and at the top of Enterprise Manager you will see the wizard and click on the index tuning wizard it is part of the profiler just like perf mon. Hope this helps.|||

Thanks Caddre, that looks promising. I'm still having a bit of difficulty - the profile isn't under Enterprise Manager/Tools, and when I go into the index tuning wizard it asks for a file that I haven't found (yet). I'll have to open up by SQL book and have a read. In the meantime I'd better close this thread off as I'm about to go on holiday, but I might be back in a few weeks.

Thanks for your help,

Robert