Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.

RO tables in SQLServer from Access

Hi all
I used the import utility to move some access tables to a SQL Svr db, and
while it all seemed to go AOK, whatever I do the resultant SQL tables appear
to be read-only. There are other tables in the SQL db that will read & write
OK.
Can any one help and tell me how to make the SQL tables writable'
thanks
JimboJim
Does the user who operates with a table have an apropriate permissions?
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:e0o7na$jib$1$8302bc10@.news.demon.co.uk...
> Hi all
> I used the import utility to move some access tables to a SQL Svr db, and
> while it all seemed to go AOK, whatever I do the resultant SQL tables
> appear
> to be read-only. There are other tables in the SQL db that will read &
> write
> OK.
> Can any one help and tell me how to make the SQL tables writable'
> thanks
> Jimbo
>
>|||Each table must have a unique column(s) (e.g. primary key) in order to be
updatable by database APIs. The SQL generated by the API includes the key
columns in the WHERE clause to ensure only one row is updated/deleted. This
is one reason why all tables should have a primary key.
IIRC, Access prompts you to specify the unique column(s) when a SQL Server
table is linked. This defaults to the primary key, if one exists.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:e0o7na$jib$1$8302bc10@.news.demon.co.uk...
> Hi all
> I used the import utility to move some access tables to a SQL Svr db, and
> while it all seemed to go AOK, whatever I do the resultant SQL tables
> appear
> to be read-only. There are other tables in the SQL db that will read &
> write
> OK.
> Can any one help and tell me how to make the SQL tables writable'
> thanks
> Jimbo
>
>

RMO Programming in VB.net

Hi,

Currently I am using the BCP functionality to import/export tables between different SQL Server 2005 databases. The process is working ok, but it creates alot of manual work, when 70 or more tables need to be transferred, every week. I am new to RMO development and am interested if the following may be possible.

- I would like to build an interface in VB.net allowing users to select a source database, and select the tables that need to be transferred from the Source DB, to the Destination DB.

-Put in place RMO funtionality to replicate the source database tables into flat files.

-Within the Vb.net app transfer the created flat files into the destination database.

I would greatly appreciate any advice on this, or if there may be a better approach at replacing the BCP functionality.

Thanks,

Since the number of tables needed will vary from user to user, then it might make sense to stick with what you have or use DTS or SSIS in some way. YOu can get creative by setting up Snapshot Replication and use your VB app to read the BCP files from the snapshot folder, but that could get tricky.

|||

Thanks Greg.

If the tables were to remain consistant would an RMO approach be feasible?

|||

Yes, if the tables were to remain constant then you could create a snapshot publication (assuming you don't need up-to-the-minute changes) and use RMO to create or sync your subscriptions. More detail/information regarding your app and business requirements would be helpful, but yes, this should work for you. You can read more about RMO in books online, just search for RMO. You can also read up on the different types of replication and what snapshot, transactional and merge replication have to offer.

|||

hi,

i saw this post & i also want to implemnt an vb.net application to provide replication.

so incase u have made the application plz guide me to make the same for my organisation.

i m also new to sql RMO

i actually made it with sql SMO

sql

RMO Programming in VB.net

Hi,

Currently I am using the BCP functionality to import/export tables between different SQL Server 2005 databases. The process is working ok, but it creates alot of manual work, when 70 or more tables need to be transferred, every week. I am new to RMO development and am interested if the following may be possible.

- I would like to build an interface in VB.net allowing users to select a source database, and select the tables that need to be transferred from the Source DB, to the Destination DB.

-Put in place RMO funtionality to replicate the source database tables into flat files.

-Within the Vb.net app transfer the created flat files into the destination database.

I would greatly appreciate any advice on this, or if there may be a better approach at replacing the BCP functionality.

Thanks,

Since the number of tables needed will vary from user to user, then it might make sense to stick with what you have or use DTS or SSIS in some way. YOu can get creative by setting up Snapshot Replication and use your VB app to read the BCP files from the snapshot folder, but that could get tricky.

|||

Thanks Greg.

If the tables were to remain consistant would an RMO approach be feasible?

|||

Yes, if the tables were to remain constant then you could create a snapshot publication (assuming you don't need up-to-the-minute changes) and use RMO to create or sync your subscriptions. More detail/information regarding your app and business requirements would be helpful, but yes, this should work for you. You can read more about RMO in books online, just search for RMO. You can also read up on the different types of replication and what snapshot, transactional and merge replication have to offer.

|||

hi,

i saw this post & i also want to implemnt an vb.net application to provide replication.

so incase u have made the application plz guide me to make the same for my organisation.

i m also new to sql RMO

i actually made it with sql SMO

Rights running Exec (SQL)

Hi all.
It looks like I need READ rights on the involved tables using Exec (SQL)
within a prosedure instead of SELECT TableName and so on.It is not enough to
have EXEC rights on the prosedure itself. Is there any way around this or do
I need go grant read rights on all the tables in the SQL string.
If so, is there a easy way within the prosedure to check if the user have
this rights and if not grant it on the fly?
Thanx all.
gh> It looks like I need READ rights on the involved tables using Exec (SQL) within a prosedu
re
> instead of SELECT TableName and so on.
I assume that you wanted to say that you need SELECT permissions on the tabl
e, that EXEC procname
isn't enough. Yes, this is one of the drawbacks of using dynamic SQL. First
step is to see if you
really need that dynamic SQL. Then see what the new features in 2005 can do
for you:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/grantperm.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.
gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL) w
ithin a prosedure
> instead of SELECT TableName and so on.It is not enough to have EXEC rights
on the prosedure
> itself. Is there any way around this or do I need go grant read rights on
all the tables in the
> SQL string.
> If so, is there a easy way within the prosedure to check if the user have
this rights and if not
> grant it on the fly?
> Thanx all.
> gh
>|||Hi Tibor.
Thanx for the document. We are close to SQL2005, but not yet there.
I ended up with creating av view with the tables in question and runned the
EXEC on this view. That worked and the tables are still "safe" and hidden
for this user.
Thank you again for your interest in this.
-gh
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL)
> within a prosedure instead of SELECT TableName and so on.It is not enough
> to have EXEC rights on the prosedure itself. Is there any way around this
> or do I need go grant read rights on all the tables in the SQL string.
> If so, is there a easy way within the prosedure to check if the user have
> this rights and if not grant it on the fly?
> Thanx all.
> gh
>

Right outer join problem

Hi,

I need your help to resolve this problem. I have written a right outer
join query between 2 indipendent tables as follows.

select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_final a
right outer join Actual_account_Tbl b on a.account_desc =
b.account_desc where (a.source_type = 'TY02' or a.source_type is
null) and (a.month = '2ND HALF' or a.month is null) and (a.year = 2004
or a.year is null) and (a.product = 'NP' or a.product is null) order
by b.Sno

But the problem is I have few records in table Actual_account_Tbl but
do not match the condition "a.account_desc = b.account_desc".

As per right outer join, I suppose to get those records as a result of
the above query with null values of a.CSPL_CSPL. But it is not
displaying.

Please help me to resolve this problem.

Regards,
OmavCould you post some code that actually reproduces the problem you describe
(DDL and sample data INSERTs). I tried the following which appears to work:

CREATE TABLE Actual_data_final (cspl_cspl INTEGER NOT NULL, month
VARCHAR(10) NOT NULL, year INTEGER NOT NULL, product CHAR(2) NOT NULL,
source_type VARCHAR(10) NOT NULL, account_desc VARCHAR(10) NOT NULL)

CREATE TABLE Actual_account_Tbl (account INTEGER NOT NULL, account_desc
VARCHAR(10) NOT NULL, sno INTEGER NOT NULL)

INSERT INTO Actual_account_Tbl VALUES (123,'ABC',0)

SELECT B.Account_desc, B.Account, A.cspl_cspl
FROM Actual_data_final AS A
RIGHT OUTER JOIN Actual_account_Tbl AS B
ON A.account_desc = B.account_desc
WHERE (A.source_type = 'TY02' OR A.source_type IS NULL)
AND (A.month = '2ND HALF' OR A.month IS NULL)
AND (A.year = 2004 OR A.year IS NULL)
AND (A.product = 'NP' OR A.product IS NULL)
ORDER BY B.sno

Result:

Account_desc Account cspl_cspl
---- ---- ----
ABC 123 NULL

Also, note that you can probably simplify the above query by putting your
WHERE criteria in the ON clause:

SELECT B.Account_desc, B.Account, A.cspl_cspl
FROM Actual_data_final AS A
RIGHT OUTER JOIN Actual_account_Tbl AS B
ON A.account_desc = B.account_desc
AND A.source_type = 'TY02'
AND A.month = '2ND HALF'
AND A.year = 2004
AND A.product = 'NP'
ORDER BY B.sno

--
David Portas
SQL Server MVP
--|||Hi,

Thanks for your help..

The following query has failed to return all the records.

SELECT b.Account_desc, b.Account,
IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
where a.source_type = 'LY01'

There are total 143 records in Actual_account_Tbl. But the above query
returned only 135 records i.e., only those records satisfy the condition
"a.Account_desc = b.Account_desc" are returned.

As per right outerjoin in the above statement I suppose to get all the
records from table 'b', and blank data from table 'a' if it doesn't
satisfy the condition.

Why it is not consistant?

Pls help me.

Thanks and Regards.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That isn't a MS SQLServer query. TSQL doesn't have an IIF function.

The problem is that you have a WHERE clause referencing the outer table. Put
the WHERE condition in the ON clause:

...
OUTER JOIN Actual_account_Tbl b
ON a.Account_desc = b.Account_desc
AND a.source_type = 'LY01'

If you need more help then post to the correct group for the product you are
using (Access?).

--
David Portas
SQL Server MVP
--|||On 28 May 2004 06:46:19 GMT, k k wrote:

>Hi,
>Thanks for your help..
>The following query has failed to return all the records.
>SELECT b.Account_desc, b.Account,
>IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
>a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
>outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
>where a.source_type = 'LY01'
>There are total 143 records in Actual_account_Tbl. But the above query
>returned only 135 records i.e., only those records satisfy the condition
>"a.Account_desc = b.Account_desc" are returned.
>As per right outerjoin in the above statement I suppose to get all the
>records from table 'b', and blank data from table 'a' if it doesn't
>satisfy the condition.
>Why it is not consistant?
>Pls help me.
>Thanks and Regards.

Hi k k,

For right (and left) joins, the place of condition matters. First, the
join is performed. Only the ON clause is checked for the join. Rows that
match are joined, rows from the second table (in case of a right join)
that don't match are added with NULLs as placeholders for the columns from
the first table.

Next, the WHERE clause is applied to the result of the join. Only the rows
in the result set that match the conditions are retained in the output.

In your case, you right joined on equality of account_desc (the
intermediate result had at least 143 rows, maybe more if there are rows in
b that match more than one row in a). Then the WHERE filter for
source_type 'LY01' discarded all rows from b without matching a, as the
outer join set source_type to NULL for these rows. The net result was the
same as an inner join would have been.

Trying to conclude what you wanted from reading your query, I think you'll
have the desired results if you just change "where" to "and". This will
make the LY01 requirement part of the join condition and you're left with
no where clause.

BTW, you're posting in a SQL Server newsgroup, but your use of IIf shows
that you're actually using MS Access. It's best to post to a newsgroup for
the product you're using, since subtle (and some less subtle) differences
between tools can cause big differences.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Right join not working when joining 3 tables

Have 2 tables that are joined by a 3rd. I want to get all the date in both
tables whether they are connected or not.
There can be many policies per role. RolePolicies is the table that
connects the two.
CREATE TABLE [dbo].[Roles] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Roles] ADD
CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
CREATE TABLE [dbo].[Policies] (
[PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Policies] ADD
CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
(
[PolicyID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RolePolicies] (
[RoleID] [int] NULL ,
[PolicyID] [int] NULL
) ON [PRIMARY]
GO
INSERT Roles (Description) VALUES ('Manager')
INSERT Roles (Description) VALUES ('Director')
INSERT Roles (Description) VALUES ('User')
INSERT Policies (Description) VALUES ('Add')
INSERT Policies (Description) VALUES ('Edit')
INSERT Policies (Description) VALUES ('Delete')
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
Here the last RIGHT JOIN works fine and we get the Delete, even though it
isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT JOIN?
If I do it this way:
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
I get the extra line ("User").
RoleID PolicyID RoleID Description
-- -- -- --
1 1 1 Manager
1 2 1 Manager
2 1 2 Director
2 2 2 Director
NULL NULL 3 User
How do I get both the "User" as well as the "Add"?
Thanks,
TomTshad,
Is it a LEFT JOIN that you want instead?
If not what should the resultset look like?
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
> Have 2 tables that are joined by a 3rd. I want to get all the date in
> both tables whether they are connected or not.
> There can be many policies per role. RolePolicies is the table that
> connects the two.
> CREATE TABLE [dbo].[Roles] (
> [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Roles] ADD
> CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
> (
> [RoleID]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Policies] (
> [PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Policies] ADD
> CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
> (
> [PolicyID]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[RolePolicies] (
> [RoleID] [int] NULL ,
> [PolicyID] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT Roles (Description) VALUES ('Manager')
> INSERT Roles (Description) VALUES ('Director')
> INSERT Roles (Description) VALUES ('User')
> INSERT Policies (Description) VALUES ('Add')
> INSERT Policies (Description) VALUES ('Edit')
> INSERT Policies (Description) VALUES ('Delete')
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> Here the last RIGHT JOIN works fine and we get the Delete, even though it
> isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT
> JOIN?
> If I do it this way:
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> I get the extra line ("User").
> RoleID PolicyID RoleID Description
> -- -- -- --
> 1 1 1 Manager
> 1 2 1 Manager
> 2 1 2 Director
> 2 2 2 Director
> NULL NULL 3 User
> How do I get both the "User" as well as the "Add"?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> Is it a LEFT JOIN that you want instead?
>
No.
That is why I showed the 2nd statement which just takes away the Join that
worked. When it was gone it worked correctly and showed the role "User".

> If not what should the resultset look like?
It should look the same as the 1st result set with one more row showing the
role "User":
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
>|||tshad,
Try using FULL JOIN:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
ON RP.POLICYID = P.POLICYID
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> No.
> That is why I showed the 2nd statement which just takes away the Join that
> worked. When it was gone it worked correctly and showed the role "User".
>
> It should look the same as the 1st result set with one more row showing
> the role "User":
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> NULL NULL 3 User NULL
> NULL
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
That worked.
I also could do it with my statement (which really is the same as yours) by
replacing both "RIGHT JOIN"s with "FULL JOIN"s, as you suggested.
Not sure why the outside RIGHT JOIN would work and not the inside one.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
This works well, but how do I tell it not to display rows that have Nulls in
the result set in certain columns.
I actually used this with 3 tables and it works fine, but I didn't want to
display rows that Nulls in 2 of the columns of the result set.
This is a little confusing, I know.
But if you take the statement and change it to:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL
ON RP.POLICYID = P.POLICYID
You will get no results.
This makes sense, because all the PolicyID records in the actual table have
something in them.
But in the full join, there can be a Null in the PolicyID if the there is a
record in Policies, but not in RolePolicies.
This would be a ridiculous statement in this example, but I would like it to
display all the rows that have no nulls.
For example,
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
I want to test for either PolicyID or RoleID, after the result set is
created.
In my other, Select - I want to test for both being NULL.
The statement would be something like:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL AND R.ROLEID <> NULL
ON RP.POLICYID = P.POLICYID
In my example, I should get the same result set, but I actually get no
results.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
that
"User".
>
-- -- -- -- -- --
--
,
>
--
>

Revoking permissions on system tables to the public role.

We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated...when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...

...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)

...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...

...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...

--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.

...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)

--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.

also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...

...I am not sure you can restrict this access and still have application functionality...

...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:

select name from sysobjects where type = 'S'

you can then use the "sp_helprotect" stored procedure to obtain the current security settings.

then use the REVOKE command to revoke public access from these objects...

...then the access methid would have to be tested again...

now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!

-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...

revoke delete permission from large no of tables

Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2
D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:

> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>
|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2

revoke delete permission from large no of tables

Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2

Reverse engineering tables

Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon
Simon
Did you mean that ans engineer is a word like 'engineer'?
This script has written by Vyas Kondreddi. See if it helps you.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Is there anyway to analyse the tables in my database and reverse engineer
> the SQL out of them?
> I really need to SQL quite badly but I can't see any easy way to get it.
> Thanks to anyone who can help
> Simon
>
|||Make use of the scripting functionality available in SQL Server Enterprise
Manager.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon

Reverse engineering tables

Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
SimonSimon
Did you mean that ans engineer is a word like 'engineer'?
This script has written by Vyas Kondreddi. See if it helps you.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Is there anyway to analyse the tables in my database and reverse engineer
> the SQL out of them?
> I really need to SQL quite badly but I can't see any easy way to get it.
> Thanks to anyone who can help
> Simon
>|||Make use of the scripting functionality available in SQL Server Enterprise
Manager.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon

Reverse engineering tables

Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
SimonSimon
Did you mean that ans engineer is a word like 'engineer'?
This script has written by Vyas Kondreddi. See if it helps you.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Is there anyway to analyse the tables in my database and reverse engineer
> the SQL out of them?
> I really need to SQL quite badly but I can't see any easy way to get it.
> Thanks to anyone who can help
> Simon
>|||Make use of the scripting functionality available in SQL Server Enterprise
Manager.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon

reverse engineering MS SQL server 2k5

Hi,
I got a DB with more than 100 tables.
i tried to get it structure by Visio and its reverse engineering method
but Visio does not draw "links" among tables representing PK and FK
connections.
So do you know any other software which could do the job ?
maybe a simple freeware is enough, it is just to have an overview on
this database and its objects.
thanks a lot,
A.Hi Alain
Visio should import these links (at least it did in SQL 2000!) Usually I
import the structure and then draw drag what is required onto a diagrams
rather than importing them directly onto diagram.
John
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||If you just want to have an overview on the database and its objects, would
the existing "Database Diagrams" feature in SQL Server 2005 be good enough
for you?
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||I had that issue also, I installed SP3 for Visio 2003 and it started working
after that :). Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
> > Hi,
> >
> > I got a DB with more than 100 tables.
> > i tried to get it structure by Visio and its reverse engineering method
> > but Visio does not draw "links" among tables representing PK and FK
> > connections.
> >
> > So do you know any other software which could do the job ?
> > maybe a simple freeware is enough, it is just to have an overview on
> > this database and its objects.
> >
> > thanks a lot,
> >
> > A.
> >|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that :). Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:
> > Hi Alan
> >
> > I can certainly import a database and foreign keys in SQL 2005, for instance
> > if I choose the northwind database and go through the wizard making sure that
> > FKs are imported and I choose the option to add the tables later to a
> > diagram. I can then sekect the customers tables from the tables and views box
> > and drag it onto the diagram. Then right clicking show related tables will
> > bring in the orders and customercustomerdemo tables with their foreign keys.
> >
> > John
> >
> > "Alain R." wrote:
> >
> > > Hi,
> > >
> > > I got a DB with more than 100 tables.
> > > i tried to get it structure by Visio and its reverse engineering method
> > > but Visio does not draw "links" among tables representing PK and FK
> > > connections.
> > >
> > > So do you know any other software which could do the job ?
> > > maybe a simple freeware is enough, it is just to have an overview on
> > > this database and its objects.
> > >
> > > thanks a lot,
> > >
> > > A.
> > >

reverse engineering MS SQL server 2k5

Hi,
I got a DB with more than 100 tables.
i tried to get it structure by visio and its reverse engineering method
but visio does not draw "links" among tables representing PK and FK
connections.
So do you know any other software which could do the job ?
maybe a simple freeware is enough, it is just to have an overview on
this database and its objects.
thanks a lot,
A.Hi Alain
Visio should import these links (at least it did in SQL 2000!) Usually I
import the structure and then draw drag what is required onto a diagrams
rather than importing them directly onto diagram.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by visio and its reverse engineering method
> but visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||If you just want to have an overview on the database and its objects, would
the existing "Database Diagrams" feature in SQL Server 2005 be good enough
for you?
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by visio and its reverse engineering method
> but visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure tha
t
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views bo
x
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by visio and its reverse engineering method
> but visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||I had that issue also, I installed SP3 for visio 2003 and it started working
after that . Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instan
ce
> if I choose the northwind database and go through the wizard making sure t
hat
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views
box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign key
s.
> John
> "Alain R." wrote:
>|||Hi
I was using Microsoft Office visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for visio 2003 and it started worki
ng
> after that . Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:
>

Reverse Engineer Hand Written SQL ( Oracle )

Hi,

My environment encompasses over 2000 tables with over 300000 lines of hand-written Oracle SQL, and what I want to do is reverse engineer the SQL to tell me what tables are being accessed and how...

I have the full list of tables stored in a reference table ( extracted from USER_TABLES ), and I also have every line of SQL loaded into another reference table, of the example format :

FILENAME, LINE_NO, SQL
products.sql,1,select *
products.sql,2,from products
products.sql,3,where id = 2
customer.sql,1,select * from
customer.sql,2,customers a, products b
customer.sql,3,where a.id = b.id

so as you can see I will be evaluating this on a line by line basis... Now this hand-written SQL will obviously be valid Oracle SQL, but it can be all over the place, in that if i am looking at it on a line-by-line basis, the INSERT, UPDATE, FROM statement may not necessary be on the same line as the table name, etc...Basically the same query can be written in many different ways...

So I am taking the list of tables ( stored in a reference table ), and then also the table holding all of the SQL code, and then basically doing a join with a LIKE clause constraint, to see what SQL lines are using what tables, but as the SQL is all hand-written the rules can get messy...

So basically what Im asking is, does anyone know of any definitive rules for reverse engineering SQL, or any example scripts, etc ? My process basically works like this at the moment to get you started :

If i find an instance of a tablename on a SQL line, it must be prefixed with either nothing, space, tab, comma, and it must be suffixed with either nothing, space, tab, comma, semicolon, right bracket... This firstly stops finding any tables thats name are part of another table, eg :

PRODUCT compared to PRODUCT_LIST

Then I make sure that it is not within a comment, so if its on a line with
/*, */, --, etc, then I disregard it ( or if it was between two sets of start-comment and end-comment i also disregard ) - eg :

/*
SELECT * FROM products
*/

After that, I then search for the word INTO, UPDATE, TRUNCATE, DROP, CREATE INDEX, RENAME on that line, etc to try and determine what I am doing to that table, and if i cant find any of those, I assume its a SELECT... I also look at the previous line for those keywords, because the SQL could have been written like this :

TRUNCATE TABLE
products

Then I also do a similar thing to look for in-line UPDATES :

UPDATE(
SELECT end_dte FROM PRODUCTS a, CUSTOMER b
) SET a.end_dte = '1-Jan-2000'

Because the above line is really updating the PRODUCT table, whereas on a line-by-line basis it looks like a SELECT...

So these are my rules in place thus far... My end result would be to have a table that says these SQL scripts access these tables in this way, for example :

FILENAME, QUERY_TYPE, TABLE
products.sql, update, products
products.sql, select, customers
products.sql, truncate, products
customers.sql, select, customers

OK, Hopefully you understand what I mean... Basically if anyone has any pre-written code or ideas that would be great, but I am more interested in expanding on these rules to make sure I get it 100% right. As you can imagine, hard-written SQL, that is technically valid SQL can still be written in many, many different ways, so the rules neeed to be very tight... Or can anyone see any flaws in my rules also ?

By the way, Im only interested in what tables are accessed, trying to do the same thing on a column basis would be WAY too hard...

Thanks,
AdamI'd use YACC to construct a tokenizer if I were you. Otherwise you'll go nuts trying to figure out how to parse the SQL. Once you've reduced it to tokens, take all of the table tokens from the token stream and you'll be more than 90% of the way to your goal.

-PatP

Returning XML values

I have an XML field in one of our tables.
A sample of the data is here:
<PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/
XMLSchema"><Item><PackageItemId>11</
PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
InstallOrder></Item></PackageAddTask>
I need to retrieve the PackageID value from this field
This query returns the entire item.
SELECT Detail.query('/PackageAddTask/Item/PackageId')
FROM Task T Where TaskTypeID = 4
This query SHOULD return the value, but instead, return NULL
Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
'int')
as Result
FROM Task T Where TaskTypeID = 4
What am I missing here?Brian Bunin,
Try:
Select Detail.value('(/PackageAddTask/Item/PackageItemId)[1]', 'int') as
Result
FROM Task T
Where TaskTypeID = 4
go
AMB
"Brian Bunin" wrote:
> I have an XML field in one of our tables.
> A sample of the data is here:
> <PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/
> XMLSchema"><Item><PackageItemId>11</
> PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
> SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
> InstallOrder></Item></PackageAddTask>
> I need to retrieve the PackageID value from this field
>
> This query returns the entire item.
> SELECT Detail.query('/PackageAddTask/Item/PackageId')
> FROM Task T Where TaskTypeID = 4
>
> This query SHOULD return the value, but instead, return NULL
> Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
> 'int')
> as Result
> FROM Task T Where TaskTypeID = 4
>
> What am I missing here?
>

Returning XML values

I have an XML field in one of our tables.
A sample of the data is here:
<PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/
XMLSchema"><Item><PackageItemId>11</
PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
InstallOrder></Item></PackageAddTask>
I need to retrieve the PackageID value from this field
This query returns the entire item.
SELECT Detail.query('/PackageAddTask/Item/PackageId')
FROM Task T Where TaskTypeID = 4
This query SHOULD return the value, but instead, return NULL
Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
'int')
as Result
FROM Task T Where TaskTypeID = 4
What am I missing here?Brian Bunin,
Try:
Select Detail.value('(/PackageAddTask/Item/PackageItemId)[1]', 'int') as
Result
FROM Task T
Where TaskTypeID = 4
go
AMB
"Brian Bunin" wrote:

> I have an XML field in one of our tables.
> A sample of the data is here:
> <PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/
> XMLSchema"><Item><PackageItemId>11</
> PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
> SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
> InstallOrder></Item></PackageAddTask>
> I need to retrieve the PackageID value from this field
>
> This query returns the entire item.
> SELECT Detail.query('/PackageAddTask/Item/PackageId')
> FROM Task T Where TaskTypeID = 4
>
> This query SHOULD return the value, but instead, return NULL
> Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
> 'int')
> as Result
> FROM Task T Where TaskTypeID = 4
>
> What am I missing here?
>

returning temp tables, t-sql stored procedures

Hi, im a bit of a beginner here and need to know how to call stored procedures from another sp and how can I return a temp table that I need to use in the calling sp's where clause?
Some code would b great.
CheersAre you looking for something like this?

create proc proc2
as
insert #tmp
select 1,'Q'
go
create proc proc1
as
create table #tmp(id int, code varchar(10))
exec proc2
select * from #tmp
go
exec proc1

or

alter proc proc2
as
select 1,'Q'
go
alter proc proc1
as
create table #tmp(id int, code varchar(10))
insert #tmp
exec proc2
select * from #tmp
go
exec proc1

or ?|||cheers thats all that is required

Returning table from UDF

Thank you David
My problem is the following:
I have certain tables to which apart from a standard number of fields
additional fields are added depending on customer requirements. A "Select *
From Table_A" statement returns all fields but because there are several
versions of the same record in the table some quite elaborate filtering
(based on current criteria) is needed to get a single record. This results
in cubersome queries. So I was wondering if I could write a UDF which would
"hide" all the complication and call it as "Select * From
UDF_A(param1,param2,..)"?
What I already have done is write a UDF returning as a table the Ids of the
records satisfying the criteria and then using that to filter the original
table ie
"Select * From Table_A where Table_A.Id IN (Select Id From UDF_X(params))"
but it is still a litle complicated for "report writers" who don't know the
internals.
Yannis
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nbidnZWNSYMQ_2zcRVn-jA@.giganews.com...
> No. Stored procedures can return variable numbers of columns though.
That's
> not to say that dynamic result sets from an SP is necessarily a good way
to
> accomplish things. Maybe if you describe your problem we can suggest a
> solution.
> --
> David Portas
> SQL Server MVP
> --
>A UDF has only one returned set. If varying return sets are needed,
multiple UDF's will have to be written. If additional fields are added to
the table in the future, those fields will have to be added to any UDF's for
the fields to appear in their return sets.
If you have a very limited param set, you can get away with making a view
for each one:
CREATE VIEW View_1 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(1))
CREATE VIEW View_2 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(2))
CREATE VIEW View_3 AS Select * From Table_A where Table_A.Id IN (Select Id
From UDF_X(3))
These views won't need modifying as the fields are added. They are very
simple for your report writers:
SELECT * FROM View_2
If you don't have a limited param set, you could try stored proc's...
CREATE PROC ShowTableA @.param1 int, @.param2 varchar(10)
AS
Select * From Table_A where Table_A.Id IN (Select Id From UDF_X(@.param1,
@.param2))
RETURN
Called by:
EXEC ShowTableA 22,'blue'
The downside here is that to capture the result in (for example) query
analyzer, you have to jump through some hoops (one of the hoops is knowing
the number and datatypes of the columns in the returned set)... maybe your
report writers are using some other tool where they don't issue sql against
the result set to do further modification? If so, this is a good choice.
"Y.Makarounis" <ymak@.otenet.gr> wrote in message
news:ONWYyJGAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Thank you David
> My problem is the following:
> I have certain tables to which apart from a standard number of fields
> additional fields are added depending on customer requirements. A "Select
*
> From Table_A" statement returns all fields but because there are
several
> versions of the same record in the table some quite elaborate filtering
> (based on current criteria) is needed to get a single record. This results
> in cubersome queries. So I was wondering if I could write a UDF which
would
> "hide" all the complication and call it as "Select * From
> UDF_A(param1,param2,..)"?
> What I already have done is write a UDF returning as a table the Ids of
the
> records satisfying the criteria and then using that to filter the original
> table ie
> "Select * From Table_A where Table_A.Id IN (Select Id From
UDF_X(params))"
> but it is still a litle complicated for "report writers" who don't know
the
> internals.
> Yannis
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:nbidnZWNSYMQ_2zcRVn-jA@.giganews.com...
> That's
> to
>