changing all permissions at once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

changing all permissions at once

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-17 : 16:31:30

i succeded only to change the permissions on 1 table at same time!

quote:

grant all SELECT, INSERT, UPDATE, DELETE
on register_detailes to user_test

is there a way to make it so in 1 line of code for the same user it will let me change the permissions for the same user?.

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-17 : 21:23:59

Run this in Query Analyser

select 'grant all SELECT, INSERT, UPDATE, DELETE on ' + name + ' to user_test'
from  sysobjects
where type = 'U'

copy the return results to another window and press F5 to run

[KH]

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 01:53:47

Or you could make the user a member of the db_datareader and db_datawriter fixed database role. That will provide SELECT, INSERT, UPDATE, DELETE permissions on all tables (including tables you create in the future)

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-18 : 04:57:21

thanks to both of u
first khtan : how can i add to you syntax so that i cant add to a user permission to run a STOREDP PROCEDURE? (beacuse as for view i understamd that giving it a permission on select is enough)

second Kristen if i do so will it let me to activate stored procedures?
and if i will oopen a table permission's i wll see that there is
X or V under the appropiate select/inseet/.... for the specific user?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-18 : 06:47:22

sysobjects.type :
U - table
P - Prodedures
V - Views
FN - Function

Just change the script to your requirement, for example store procedures

select 'grant EXECUTE on ' + name + ' to user_test'
from sysobjects
where type = 'P'

[KH]

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 09:55:06

"second Kristen if i do so will it let me to activate stored procedures?"

No, there is no generic fixed role that provides EXECUTE permission on all Sprocs, you have to do that explicitly.

"and if i will oopen a table permission's i wll see that there is
X or V under the appropiate select/inseet/.... for the specific user?"

I've never actually tried that!

In case you are new to it, and I can explain it well enough! :

The permissions on tables and views etc. used by a stored procedure will be those of the user that CREATED the Sproc. Sproc has nothing to do with user permissions on tables that the Sproc uses (unless the SProc uses dynamic SQL), so if the user has EXECUTE permission on the SProc then they can do whatever the Sproc does.

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-20 : 01:50:34

khtan i did as you wrote but it has changed permissions only on sysobject tables where i want to change the permissions an all the tables i have created - so how do i do it?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-20 : 06:58:40

All the tables that you created will exists in sysobjects. sysobjects contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database (DB).

Which means, if the tables that you created is in another DB you have to run the script for every DB.

[KH]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-20 : 11:03:21

i tried khtan code and it didnt change the permission on any of the tables of the DB!!!
its only show me a column with :
'grant all SELECT, INSERT, UPDATE, DELETE on table1 to user_test'
which actually didnt do anything:(
i tried to do :


select *
from  sysobjects
where type = 'U'

and i got list of all the table
but still didnt succedd in changing all the tables under thesame DB for a specific user any ideas?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2005-11-20 : 11:06:21

You need to execute the code that khtan's statement generates. it only generates the script for you, you have to actually run it.

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-20 : 11:08:41

:) You're supposed to take the results, paste them into a new window, and execute that. Did you read this part?

quote:

copy the return results to another window and press F5 to run

Also, you don't need the "all" in this:

quote:

select 'grant all SELECT, INSERT, UPDATE, DELETE on ' + name + ' to user_test'
from sysobjects
where type = 'U'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-20 : 11:22:39

i tried it and i got on all the rows(as the number of table) the same lines :

quote:
grant SELECT on register_detailes to user_test

paste it into another window and got onl error that register_detailes is invalied object
i asume i am doing again something wrong any idea?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2005-11-20 : 11:30:12

I tried khtan's code here and got a different table name on every row.

"paste it into another window and got onl error that register_detailes is invalied object"

Make sure your "other window" is attached to the same / correct database.

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-20 : 12:02:02

i tried again :(
i tried again to run it in the query analayzer but from start i recive all the rows the same!
first thing i need to pass this beacuse i recive on all the rows the same select!(and i understand in each select should e the table name-maybe some 1 know an article which show's step by step how do do it maybe i am wrong from the begining }-: )
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2005-11-20 : 12:03:34

What does

select [name]
from sysobjects
where type = 'U'
ORDER BY [name]

give you? A complete list of all your tables, or something else?

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-20 : 12:09:23

yes it didi!!!
wait did the word in the select u gave me the word "name" wasnt i suppose to replace it with the db name?
maybe the problem is there?
or its not?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-20 : 15:47:39

ok worked at last still dosent understand fully why :


select 'grant all SELECT, INSERT, UPDATE, DELETE on ' + name + ' to user_test'
from  sysobjects
where type = 'U'

where the "name" i left as is
can any 1 explain why (i thought that the name is the name of the db)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-20 : 21:40:36

quote:
select name
from sysobjects
where type = 'U'

gives you the names of all user tables in the database. If you ran the same query with type = 'P', it would give you all the user stored procedures in the database. The other part appends text to the table names and creates your "script". You can see all the types by looking up sysobjects in Books Online.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-21 : 00:59:09

If you are lazy to COPY & PASTE like me, i normally use the script below and just change the @db and @usr to the database & user and run it. It is making use of the xp_execresultset. Not very sure is it still supported in SQL 2005. Anyone can comment on this ?


create table #sql_cmd
(
	cmdrow	int	identity not null,
	cmdtext	nvarchar(4000)	not null,
	primary key (cmdrow)
)

declare	
	@db 	varchar(30),
	@usr	varchar(30)

select @db  = 'NorthWind'
select @usr = 'user_01'
delete #sql_cmd
insert into #sql_cmd(cmdtext) select 'GRANT ALL ON ' + name + ' TO ' + @usr from sysobjects where type = 'U'		-- tables
insert into #sql_cmd(cmdtext) select 'GRANT ALL ON ' + name + ' TO ' + @usr from sysobjects where type = 'V'		-- views
insert into #sql_cmd(cmdtext) select 'GRANT EXECUTE ON ' + name + ' TO ' + @usr from sysobjects where type = 'P'	-- store procedure
insert into #sql_cmd(cmdtext) select 'GRANT EXECUTE ON ' + name + ' TO ' + @usr from sysobjects where type = 'FN'	-- function

exec master..xp_execresultset N'select cmdtext from #sql_cmd order by cmdrow', @db

[KH]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-11-21 : 01:50:59

first thanks alot all of u
1 last small question : what does the DRI column means?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-21 : 21:23:39

sorry don't quite get it... what DRI column ?
This DRI ?
DRI = Declarative Referential Integrity
See the link for details
[url]http://www.windowsitpro.com/SQLServer/Article/ArticleID/6200/6200.html[/url]

[KH]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources