|
The readability would be a matter of an opinion 
If you set up your results as in your post, you could have one very long row (depending on how many users you have).
Perhaps, you could get a starting point if you take tables as rows and then users for each table with fixed columns as privileges. Something like the following. Then if you like you can start pivoting on tables or users using sub selects in FROM section and modify the structure as you like.
Also notice that you can place FOR XML AUTO at the end of the query, if it makes the results more readable.
select userinfo.Table_Name,
userinfo.Grantee ,
case when selectinfo.GRANTOR is not null then 'Yes' else '' end SelectPermission,
case when insertinfo.GRANTOR is not null then 'Yes' else '' end InsertPermission,
case when updateinfo.GRANTOR is not null then 'Yes' else '' end UpdatePermission,
case when updateinfo.GRANTOR is not null then 'Yes' else '' end DeletePermission
from information_schema.table_privileges as userinfo
left outer join
information_schema.table_privileges as selectinfo
on userinfo.grantee = selectinfo.grantee
and userinfo.table_catalog = selectinfo.table_catalog
and userinfo.table_schema = selectinfo.table_schema
and userinfo.table_name = selectinfo.table_name
and selectinfo.PRIVILEGE_TYPE = 'SELECT'
left outer join
information_schema.table_privileges as insertinfo
on userinfo.grantee = insertinfo.grantee
and userinfo.table_catalog = insertinfo.table_catalog
and userinfo.table_schema = insertinfo.table_schema
and userinfo.table_name = insertinfo.table_name
and insertinfo.PRIVILEGE_TYPE = 'INSERT'
left outer join
information_schema.table_privileges as updateinfo
on userinfo.grantee = updateinfo.grantee
and userinfo.table_catalog = updateinfo.table_catalog
and userinfo.table_schema = updateinfo.table_schema
and userinfo.table_name = updateinfo.table_name
and updateinfo.PRIVILEGE_TYPE = 'UPDATE'
left outer join
information_schema.table_privileges as deleteinfo
on userinfo.grantee = deleteinfo.grantee
and userinfo.table_catalog = deleteinfo.table_catalog
and userinfo.table_schema = deleteinfo.table_schema
and userinfo.table_name = deleteinfo.table_name
and deleteinfo.PRIVILEGE_TYPE = 'DELETE'
order by Table_Name, Grantee
The need to optimize rises from a bad design
|
|
|
|
|
I believe the answer to your problem is to use a Pivot query in SQL 2005. I'm not sure how to due this, but it's worth investigating.
|
|
|
|
|
Hi
How to remove Identity(1,1) property only from column in table in sql server 2000.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
You must recreate the table so:
- get the data into another table
- drop the table
- create the table without identity specification
- place the data back to the new table
The need to optimize rises from a bad design
|
|
|
|
|
try a google search for "drop identity constraint tsql"
|
|
|
|
|
Do you have any specific article in mind? Note that this was for version 2000.
The need to optimize rises from a bad design
|
|
|
|
|
I generally find SQL Server Books OnLine is an ideal place to start with help with SQL Server but that is just me.
|
|
|
|
|
Yeah, but I'm under the impression that in version 2000 the identity can only be disabled. Constraints can be dropped, but is identity modelled as constraint in 2000? Didn't find any clear answer for that.
The need to optimize rises from a bad design
|
|
|
|
|
I have a 'product' table, with an integer foreign key into a 'productLine' table. I populate a Products menu with one child item for each product line, and when that menu item is clicked, an image gallery for that product line opens. My issue is that I need the product line name on the gallery page, so I must either pass the productLineId in the url, and use it as a filter parameter in my product image query, and perform a separate lookup to get the product line name, or join the two tables, and pass the product line name as part of the url. I don't feel comfortable with using the join only for this reason. What are the general opinions on a matter like this?
|
|
|
|
|
Personally, unless there is a very good reason not to, I would pass the name and id. You need the id to look up the images, but database access is relatively expensive so I would avoid the round trip just to get the name.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: I would avoid the round trip just to get the name.
I'm already avoiding the round trip by using a denormalised view, where each product record includes the product name view.
|
|
|
|
|
Well there you go, you are doing it the way I would. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How many of these lookups do you have? It seems likely your data layer would be caching them anyway...
|
|
|
|
|
I have file in macro word name : Dataku.
is anybody could tell me, how to execute that file from sql 2005.
Thanks before. 
irvinia
|
|
|
|
|
irvinia wrote: I have file in macro word name : Dataku.
is anybody could tell me, how to execute that file from sql 2005.
Are you saying you want to run a macro in MS Word from within your database? I don't think that is possible...
|
|
|
|
|
Yes, i want to run macro in MS Word from database, and maybe that's not possible. ok, i will try looking for other way.
thank u for your replay, ChandraRam
|
|
|
|
|
If you're trying to use the article you mentioned before(www.codeproject.com/KB/miscctrl/data_dictionary.aspx[^]), the idea is that you place the macro in a new Word document (using macro editor), modify it and run it.
If it is something else you're after, please describe with more details.
The need to optimize rises from a bad design
|
|
|
|
|
Thank you for your replay.
i just try to change that code from that site to sql statement (maybe it's not simple), because i'm looking for the same method but with different language.
anyway, thanks again, Mike. i will looking for other way... 
|
|
|
|
|
hi all,
i simply write a small code of piece which create backup of Sql server database. It works well but whenever i set the target path of back file to Desktop( or program files folder) then it gives following error
"Can not open backup device" & Target Path & ". Operating system error 5(Access is denied) /or Operating system error 3(Error not found).
Any body can help me to resolve this error ?
|
|
|
|
|
Hi Rupesh,
The backup crated by the sql server can be stored only on the machine where the SQL server is installed. Just create a folder in SQLServer and give full permission to this folder and then try to create the backup. I hope it will work.
Thx
|
|
|
|
|
hi all,
In my application i hav a search page.
I have a table with 5 Columns..
Columns are:
p_id,p_code,p_bedNumber,p_bathNumber,p_price
Table:
p_id p_code p_bedNumber p_bathNumber p_price
1 a1 2 3 100000
2 a2 3 4 210000
3 a3 2 1 12000
the search parameter may containe multi valuese for @p_bedNumber
ie. @p_bedNumber='3,4'
i want to display all records which has 3 or 4 in p_bedNumber column.
In this case only return 1 record..
I 'm looking to write a procedure plz help
|
|
|
|
|
Ha_80 wrote: the search parameter may containe multi valuese for @p_bedNumber
ie. @p_bedNumber='3,4'
i want to display all records which has 3 or 4 in p_bedNumber column.
try following where clause in query
where @p_bedNumber in ('3','4')
hope this helps
|
|
|
|
|
select * fro mytable where (p_bedNumber = 3) or (p_bedNumber = 4)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
SELECT
column1,
column2,
if (column3 = 0)
begin
'Z',
end
else
begin
'C',
end
FROM table1
Is it possible that i can do it this way?
KaKaShi HaTaKe
|
|
|
|
|
Now, a question for YOU. Which takes longer, typing it in to CP and waiting for a reply, or typing in into query analyser and running it?
A clue, try the CASE statement.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
