Monday, March 19, 2012
Dump of SQL permissions?
Is there a way to get a list of all assigned permissions in a DB? I'd like
to see it in a format like this:
Database X
Object Name Roles Users
Object X role X user1
user2
user 3
role Y user 1
user 3
user 25
etc
I came up with this, but it will not differentiate between Grants and
Denies, nor will it be formatted the way I need:
select so.Name ObjectName, su.Name UserName
from sysObjects so
inner join sysPermissions sp on so.id = sp.id
inner join sysUsers su on sp.grantee = su.uid
where so.name not like 'dt_%' and so.name not like 'sys%'
order by 1,2
TIA, ChrisRThis should give you the info you need, but you may need to rearrange the
output format.
(NOTE: The WHERE clause at the end just prevents listing the built-in
database roles, such as, db_owner)
select Users.UserName, Users.UserType, Users.SQLRoleName,
ObjPerm.Permission, ObjPerm.ObjName
from
(
select U.name UserName,
case when U.isntname = 1 and U.isntgroup <> 1 then 'NTuser'
when U.isntgroup = 1 then 'NTgroup'
when U.issqluser = 1 then 'SQLuser'
when U.issqlrole = 1 then 'SQLrole' end UserType, '' [SQLRoleName]
from dbo.sysusers U
union
select U.name, 'MemberOf' UserType, G.name [SQLRoleName]
from dbo.sysmembers M inner join dbo.sysusers U
on M.memberuid = U.uid
inner join dbo.sysusers G
on M.groupuid = G.uid
) Users
full join
(
select O.name ObjName, U.name UserName, (TY.name + ' ' + AC.name) Permission
from dbo.sysprotects PR inner join master.dbo.spt_values AC on PR.action =
AC.number
inner join master.dbo.spt_values TY on PR.protecttype = TY.number
inner join dbo.sysobjects O on PR.id = O.id
inner join dbo.sysusers U on PR.uid = U.uid
where AC.type = 'T' and TY.type = 'T'
) ObjPerm
on (Users.UserName = ObjPerm.UserName or Users.SQLRoleName = ObjPerm.UserNam
e)
where Users.UserName not like('db_%')
--
DJanson
"ChrisR" wrote:
> SQL2K sp4
> Is there a way to get a list of all assigned permissions in a DB? I'd like
> to see it in a format like this:
> Database X
> Object Name Roles Users
> Object X role X user1
> user2
> user 3
> role Y user 1
> user 3
> user 25
> etc
>
> I came up with this, but it will not differentiate between Grants and
> Denies, nor will it be formatted the way I need:
> select so.Name ObjectName, su.Name UserName
> from sysObjects so
> inner join sysPermissions sp on so.id = sp.id
> inner join sysUsers su on sp.grantee = su.uid
> where so.name not like 'dt_%' and so.name not like 'sys%'
> order by 1,2
> TIA, ChrisR
>
Dump of SQL permissions?
Is there a way to get a list of all assigned permissions in a DB? I'd like
to see it in a format like this:
Database X
Object Name Roles Users
Object X role X user1
user2
user 3
role Y user 1
user 3
user 25
etc
I came up with this, but it will not differentiate between Grants and
Denies, nor will it be formatted the way I need:
select so.Name ObjectName, su.Name UserName
from sysObjects so
inner join sysPermissions sp on so.id = sp.id
inner join sysUsers su on sp.grantee = su.uid
where so.name not like 'dt_%' and so.name not like 'sys%'
order by 1,2
TIA, ChrisRThis should give you the info you need, but you may need to rearrange the
output format.
(NOTE: The WHERE clause at the end just prevents listing the built-in
database roles, such as, db_owner)
select Users.UserName, Users.UserType, Users.SQLRoleName,
ObjPerm.Permission, ObjPerm.ObjName
from
(
select U.name UserName,
case when U.isntname = 1 and U.isntgroup <> 1 then 'NTuser'
when U.isntgroup = 1 then 'NTgroup'
when U.issqluser = 1 then 'SQLuser'
when U.issqlrole = 1 then 'SQLrole' end UserType, '' [SQLRoleName]
from dbo.sysusers U
union
select U.name, 'MemberOf' UserType, G.name [SQLRoleName]
from dbo.sysmembers M inner join dbo.sysusers U
on M.memberuid = U.uid
inner join dbo.sysusers G
on M.groupuid = G.uid
) Users
full join
(
select O.name ObjName, U.name UserName, (TY.name + ' ' + AC.name) Permission
from dbo.sysprotects PR inner join master.dbo.spt_values AC on PR.action =AC.number
inner join master.dbo.spt_values TY on PR.protecttype = TY.number
inner join dbo.sysobjects O on PR.id = O.id
inner join dbo.sysusers U on PR.uid = U.uid
where AC.type = 'T' and TY.type = 'T'
) ObjPerm
on (Users.UserName = ObjPerm.UserName or Users.SQLRoleName = ObjPerm.UserName)
where Users.UserName not like('db_%')
--
DJanson
"ChrisR" wrote:
> SQL2K sp4
> Is there a way to get a list of all assigned permissions in a DB? I'd like
> to see it in a format like this:
> Database X
> Object Name Roles Users
> Object X role X user1
> user2
> user 3
> role Y user 1
> user 3
> user 25
> etc
>
> I came up with this, but it will not differentiate between Grants and
> Denies, nor will it be formatted the way I need:
> select so.Name ObjectName, su.Name UserName
> from sysObjects so
> inner join sysPermissions sp on so.id = sp.id
> inner join sysUsers su on sp.grantee = su.uid
> where so.name not like 'dt_%' and so.name not like 'sys%'
> order by 1,2
> TIA, ChrisR
>
Sunday, March 11, 2012
Dumb Data Storage Question
Hello,
So, here's my dumb question; if I wanted to store some *.gif images in some database (SQL2K possibly 2K5) field and wanted to pull the information from that to display on the web form, am I actually storing the image in the database or am I storing the location of the image in the database?
I ask this because I was under the impression that the location to the image file is what was being stored but another person was saying that it was the actual image. I guess I'm confused...
Thanks in advance...
So if I wanted to store an the actual image in a DB table, I'd store it as a hexidecimal date type, correct? That makes sense...
So in order to get that image into the table in the first place I could just do an upload from a web UI, correct?
Thanks!
|||Kind of. Please check documentation regarding Image data types. Also do some googling and understand the consequences of storing images in databases. Databases are better storing data than images.|||Cool. Thanks.
I agree with you, for whatever that's worth. I would have just put a location path in the db to pull the images in but thought if you can just store the whole image, why not do it that way? But I think the path is the way to go. especially if you've standardized the path of the images to a specific location.
Thanks again!