is this enough to copy user's permissions to another user's from sp_helpprotect

Hi I use exec helprotect to put a user's perimssion into a temp table called #permissions defined with these columns : (DBname,[Owner] ,[Object],[Grantee] , [Grantor] , [ProtectType] , [Action] , [Col] ) like this:

Now when I get those permissions I use this script inside a cursor to replicate each permission to the user: @.newLoginParam

My question: is there any big business scenario in which my script might fail or miss some permissions for any user?

here is the main part of my script:

declare @.objj as varchar(8000)
declare @.grantee as varchar(8000)
declare @.action as varchar(8000)
declare @.col as varchar(8000)
declare @.sqlGrant as varchar(8000)
declare @.ProtectType as varchar(8000)
declare @.count as int
set @.count=0
declare crsMyTblPermis cursor for
select Object,Grantee,ProtectType , Action, Col from #permissions
open crsMyTblPermis
fetch next from crsMyTblPermis into @.objj, @.grantee, @.ProtectType, @.action,@.col
while @.@.fetch_status=0
begin

if (@.objj!='.' and (@.col = '.' OR @.col = '(All+New)'))
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + ' to ' + @.newLoginParam
end

if (@.objj!='.' and @.col != '.' and @.col != '(All+New)')
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + '(' + @.col + ') to ' + @.newLoginParam
end

if (@.objj='.')
begin
set @.sqlGrant = @.ProtectType +@.action + ' to ' + @.newLoginParam
end

exec(@.sqlGrant)
fetch next from........

...etc

P.S: The main reason is that I can t test my script now on the production platform on which the script will be run

Thanks for your advice .

I think you nead a space between @.ProtectType and action.

If you need to grant the permission of an existing user to many new users consider using a database role. You can grant the permissions to the role and then just add the new users to the role.

|||

yeah i will also add the new user to the roles that the old user was part of as well. But i guess the old user might also have permissions on databse objects without necessarily belonging to a specific role?do you agree?

Thanks

is this a bad idea? (replicating from pub to dist, back to pub)

We need to create a copy of a db with updates on the same publisher.
Problem is that the publisher is already using a separate distributor
server. Since you can only have 1 distributor per publisher we are in a
bind here. We can't change the distributor because it's handling
replication for other publishers on this box. So we were thinking that
we just replicated the db to the publisher via the separate distributor.
Make sense? Is this a bad idea since technically it's leaving the
machine traveling across the network to the distributor then back down
to the exact same machine it came from.
What other solution might we use? They do not want to dts it for some
reason. They want it to replicate. Not sure why
tia
-comb
Comb,
this is a possible topology and I agree with what you're saying re
transactional replication, but merge replication and snapshot will not send
data via the distributor. As alternatives, you could also investigate
database mirroring to a separate instance (+ database snapshot if they want
to run reports) on the same box or a custom log shipping solution. It
depends on the business requirements really.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <OKh7ap1QGHA.3052@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> this is a possible topology and I agree with what you're saying re
> transactional replication, but merge replication and snapshot will not send
> data via the distributor. As alternatives, you could also investigate
> database mirroring to a separate instance (+ database snapshot if they want
> to run reports) on the same box or a custom log shipping solution. It
> depends on the business requirements really.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Paul
Thanks for your response.
I am going to look into database mirroring and database snapshot right
now. I am not a sql dude (i am a network guy that gets stuck with
messing with replication). For some reason, i was thinking that those
two terms were only part of sql2005. I will do my research though and
make sure.
I guess one other issue is that this db is 300+gb and they only want a
few tables out of it.
thanks again paul.
-comb
|||Comb,
for a custom log shipping solution you'd be ok with sql server 2000. Apart
from that you're right that you'd need sql 2005 for a database mirroring
solution. The point about mirroring is that it is available but not
currently supported, so that might be a factor. if it's a case of just
needing a duplicate of the complete database, I'd script up a solution for
log shipping, or use some of the free scripts out there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <#PB2d75QGHA.4696@.tk2msftngp13.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> for a custom log shipping solution you'd be ok with sql server 2000. Apart
> from that you're right that you'd need sql 2005 for a database mirroring
> solution. The point about mirroring is that it is available but not
> currently supported, so that might be a factor. if it's a case of just
> needing a duplicate of the complete database, I'd script up a solution for
> log shipping, or use some of the free scripts out there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Paul.
Thanks for your response. You wouldn't happen to have any of those free
scripts on your replicationanswers site would you?
tia
-comb
|||This should help:
http://www.sql-server-performance.co...g_shipping.asp
The other place I've seen them is on the resource kit - which you'll have if
you've got MSDN at work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <#FkCWncRGHA.4952@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> This should help:
> http://www.sql-server-performance.co...g_shipping.asp
> The other place I've seen them is on the resource kit - which you'll have if
> you've got MSDN at work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
thanks again paul.

Is there anything like rowid, rownum like in MySql and Oracle?

Hi,

i am new to SQL Server. I want to write a query where in i want to delete duplicate rows from a table keeping the master copy.

If it is MySQL or Oracle we can write that using built in rownum or rowid. How to do that task in SQL Server 2005. Is there anything like rowid, rownum in SQL Server? If not suggest me a way to do that?

...aazad

not exactally , but u can make use of 'TOP' or row_number() function..

select top 1 from table 1 order by column1

u can use top intelligently to get top/bottom nth row... given u have somethin to orderby

|||Rownum is a psuedo column that generates a logical sequence number so it will change depending on the query execution plan, data etc. Rowid on the other hand is a physical identifier (at least in Oracle). So how are you using these in your queries? What is the purpose of using something like ROWID? You do have primary key or unique key constraints on your tables right! It will be easier to suggest the alternatives if we know your use cases.|||

Hi chandar,

There can be a senario where a table has no primary key and has data. Later when i want to make a column as primary key, i need to delete the duplicates, which i dont want to do it manually. so i shud write a query where i can delete duplicate rows keeping one copy of it. I worked with MySQL and in MySQL i can write a query as follows

delete from test where rowid in ( select rownum from test where rownum not in ( select min(rownum) from test group by all_columns having count(*) > 1 ) group by all_columns having count(*) > 1

The above code deletes the duplicates the master copy in MySQL. I am using that logical column rownum. How to do the same job in SQL Server 2005?

Regards..,

Aazad

|||

okk...lect us say u want to make column1 as ur primary key in table1 , so to find out the duplicate(or more) entries of this key , use the following query...

select column1 from table1

group by column1

having count(column1)>1

this will enlist all the entries for column1 which r repeating...

|||

Thank god .. you are using SQL Server 2005 use the following query

Example:

CREATE TABLE Table1

(

[Id] [int] NULL

)

go

INSERT INTO Table1 values(10);

INSERT INTO Table1 values(10);

INSERT INTO Table1 values(20);

INSERT INTO Table1 values(20);

go

With Test(rownum,ID)

as

(

Select Row_Number() OVER (ORDER BY ID), * From Table1

)

Delete From Test Where rownum in

(

Select A.rownum From Test A JOIN Test B On B.Id=A.ID and A.rownum >= B.rownum

Group bY A.rownum,A.ID Having Count(A.ID) <> 1

)

|||hi
use newid() function
good luck

Is there any option to create a formula object by copy paste

Friends,
crystal report 8.0
I really fed with creating formulas in crystal report. there have hundreds of formula. and almost same formula is there but I couldn't copy this to new.
please help me. thanks in advance.Can you give more information on where you are having problem?

is there an equivalent table copy command in ms sql a la oracle?

is there a command in ms sql server 2000 equivalent to this oracle table copy command?
create table myTable_bak as select * from myTable;I guess you can do it as
select * into table1 from table2 which is a bulk copy.