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