Thanks Hari, That helps a lot.
Do I have a way to know previlage details about an Object?
such as is the SELECT on a table been Granted, revoked or
denied?
Greg Chang
Subject: Re: Questions about syspermissions
From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
6/24/2004 9:29:17 PM
HI,
Have a look into sysprotects table, column action
contains the granted
previlages for each objects.
The explanation for Action column:-
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
Simply you can execute the below system procedure to get
the objct level
previlages:-
sp_helprotect <object_name>
Thanks
Hari
MCDBA
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message
news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
> is syspermissions table contains the Permissions of
> delete, select, insert, update, execute and DRI on all
> the objects?
> how do I use this syspermissions table? For example,
> which column stored the INSERT Privileges?
> thanks
> Greg Chang
..
See the BOL for information about the sp_helprotect stored procedure.
Jim
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
> Thanks Hari, That helps a lot.
> Do I have a way to know previlage details about an Object?
> such as is the SELECT on a table been Granted, revoked or
> denied?
> Greg Chang
>
> Subject: Re: Questions about syspermissions
> From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
> 6/24/2004 9:29:17 PM
> HI,
> Have a look into sysprotects table, column action
> contains the granted
> previlages for each objects.
> The explanation for Action column:-
> 26 = REFERENCES
> 178 = CREATE FUNCTION
> 193 = SELECT
> 195 = INSERT
> 196 = DELETE
> 197 = UPDATE
> 198 = CREATE TABLE
> 203 = CREATE DATABASE
> 207 = CREATE VIEW
> 222 = CREATE PROCEDURE
> 224 = EXECUTE
> 228 = BACKUP DATABASE
> 233 = CREATE DEFAULT
> 235 = BACKUP LOG
> 236 = CREATE RULE
>
> Simply you can execute the below system procedure to get
> the objct level
> previlages:-
> sp_helprotect <object_name>
> --
> Thanks
> Hari
> MCDBA
> "Greg Chang" <anonymous@.discussions.microsoft.com> wrote
> in message
> news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
>
> .
>
|||What is BOL?
>--Original Message--
>See the BOL for information about the sp_helprotect
stored procedure.
>Jim
>"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
Object?[vbcol=seagreen]
or[vbcol=seagreen]
get[vbcol=seagreen]
wrote[vbcol=seagreen]
all
>
>.
>
|||See the BOL for information about the sp_helprotect stored procedure.
Jim
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
> Thanks Hari, That helps a lot.
> Do I have a way to know previlage details about an Object?
> such as is the SELECT on a table been Granted, revoked or
> denied?
> Greg Chang
>
> Subject: Re: Questions about syspermissions
> From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
> 6/24/2004 9:29:17 PM
> HI,
> Have a look into sysprotects table, column action
> contains the granted
> previlages for each objects.
> The explanation for Action column:-
> 26 = REFERENCES
> 178 = CREATE FUNCTION
> 193 = SELECT
> 195 = INSERT
> 196 = DELETE
> 197 = UPDATE
> 198 = CREATE TABLE
> 203 = CREATE DATABASE
> 207 = CREATE VIEW
> 222 = CREATE PROCEDURE
> 224 = EXECUTE
> 228 = BACKUP DATABASE
> 233 = CREATE DEFAULT
> 235 = BACKUP LOG
> 236 = CREATE RULE
>
> Simply you can execute the below system procedure to get
> the objct level
> previlages:-
> sp_helprotect <object_name>
> --
> Thanks
> Hari
> MCDBA
> "Greg Chang" <anonymous@.discussions.microsoft.com> wrote
> in message
> news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
>
> .
>
|||BOL = Books OnLine, which is the documentation for SQL Server. You can find
it in the SQL Server program group, and you can download the latest updated
version from www.microsoft.com/sql
Jacco Schalkwijk
SQL Server MVP
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...[vbcol=seagreen]
> What is BOL?
> stored procedure.
> in message
> Object?
> or
> get
> wrote
> all
|||What is BOL?
>--Original Message--
>See the BOL for information about the sp_helprotect
stored procedure.
>Jim
>"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
Object?[vbcol=seagreen]
or[vbcol=seagreen]
get[vbcol=seagreen]
wrote[vbcol=seagreen]
all
>
>.
>
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...
> What is BOL?
BOL stands for Books On Line, the official on-line guide for SQL Server, you
can download, in localized version at
http://www.microsoft.com/sql/techinf...2000/books.asp
it's the best documentation ever about SQL Server...
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
|||BOL = Books OnLine, which is the documentation for SQL Server. You can find
it in the SQL Server program group, and you can download the latest updated
version from www.microsoft.com/sql
Jacco Schalkwijk
SQL Server MVP
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...[vbcol=seagreen]
> What is BOL?
> stored procedure.
> in message
> Object?
> or
> get
> wrote
> all
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...
> What is BOL?
BOL stands for Books On Line, the official on-line guide for SQL Server, you
can download, in localized version at
http://www.microsoft.com/sql/techinf...2000/books.asp
it's the best documentation ever about SQL Server...
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
Showing posts with label helps. Show all posts
Showing posts with label helps. Show all posts
Friday, March 30, 2012
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
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
Subscribe to:
Posts (Atom)