User Problem
-
Suppose i have 100 DB each having 100 Tables and 500 SPs.
Now i want to create a user that can SELECT,ADD,UPDATE,DELETE the data of all the TABLES of all the DBs and can EXECUTE all the SPs of all the DBs.
Can some one tell me the best way to do this, kindly tell me the syntax
-
-- Create Login
exec sp_addlogin @user, @password, @default_db
-- Grant DB access
exec db_name..sp_grantdbaccess 'kh'
for each database, execute the following
-- Grant Table / View rights
GRANT ALL ON table_view_name TO @user
-- Grant Execute on SP / FN
GRANT EXECUTE ON sp_fn_name to @user
-
Thanks for replying.....but sorry to tell u that i knew this script....wat i want to know is .....is there any single command that can grant ALL to all the tables of a DB i.e
GRANT ALL (ALL TABLES OF A DB) TO @USER
similarly that can grant EXECUTE to all the procedures of a DB i.e
GRANT EXECUTE (ALL PROCS OF A DB) TO @USER
The reason being....as i said b4...that i have alot of DBs....than alot of tables and alot of SPs in each DB......so u can imagine the hactic work that is required for writing GRANT for each object of the DBs........although i know that it could be simplified using system tables..(selecting table names and sp names of a DB from system tables....then using cursors or any thing else to create GRANT commands for each object)
-
Yes. Replace 'user1' with the actual user
exec master..xp_execresultset N'select ''GRANT ALL ON '' + name + '' TO user1'' from sysobjects where type in (''U'', ''V'')', @database_name
-
KH i am getting the following error when i execute the above statement...isnt it strange
Incorrect syntax near 'Stored'
-
how about :
-- grant select, insert, update, delete
DECLARE @SQLStatement nvarchar(4000)
DECLARE csrGrants CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
Select 'Grant select, insert, update, delete on [' + [TABLE_SCHEMA] + '].[' + [TABLE_NAME] + '] to YourUser '
from [INFORMATION_SCHEMA].[TABLES]
where [TABLE_SCHEMA] = 'xxx'
-- and [TABLE_NAME] like 'spc%'
order by [TABLE_SCHEMA] ,[TABLE_NAME]
OPEN csrGrants
WHILE 1 = 1
BEGIN
FETCH NEXT FROM csrGrants INTO @SQLStatement
IF @@FETCH_STATUS = -1 BREAK
EXEC(@SQLStatement)
END
CLOSE csrGrants
DEALLOCATE csrGrants
-- grant spc-s
--DECLARE @SQLStatement nvarchar(4000)
DECLARE csrGrants CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
Select 'Grant execute on [' + [SPECIFIC_SCHEMA] + '].[' + [SPECIFIC_NAME] + '] to YourUser '
from [INFORMATION_SCHEMA].[ROUTINES]
where [SPECIFIC_SCHEMA] = 'xxx'
-- and [SPECIFIC_NAME] like 'spc%'
order by [SPECIFIC_SCHEMA] , [SPECIFIC_NAME]
OPEN csrGrants
WHILE 1 = 1
BEGIN
FETCH NEXT FROM csrGrants INTO @SQLStatement
IF @@FETCH_STATUS = -1 BREAK
EXEC(@SQLStatement)
END
CLOSE csrGrants
DEALLOCATE csrGrants
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Suppose my DB name is MYDB.....
Can u plz explain the FROM and WHERE clauses......i.e
from [INFORMATION_SCHEMA].[TABLES]
where [TABLE_SCHEMA] = 'xxx'
AND
from [INFORMATION_SCHEMA].[ROUTINES]
where [SPECIFIC_SCHEMA] = 'xxx'
-
Well, for sqlserver < ss2005 "specific_schema" = "owner"
If you don't want to filter based on owner, just comment the where clause.
you can run the script preceded by :
Use MYDB
go
place the script here
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Usman Tanveer,
can you post your sql statment ?
are you using sa to execute the statement ?
-
This is for KH
exec master..xp_execresultset N'select ''GRANT ALL ON '' + name + '' TO test'' from sysobjects where type in (''U'', ''V'')', 'ADL'
HERE 'ADL' is my DB Name and 'test' is the username
THIS IS FOR alzdba
Verryyyyyyyy Strange..................your solution worked.....but i could not understand that how it works...
can u please explain jus for my knowledge
I want the explanation of the following SELECT Statement ONLY
Select [TABLE_SCHEMA], [TABLE_NAME]
from [INFORMATION_SCHEMA].[TABLES]
Kindly explain about the
[TABLE_SCHEMA]
[TABLE_NAME]
[INFORMATION_SCHEMA].[TABLES]
-
Check Information Schema Views in books online.
They are SQL-92 standard.
These views are preferable over the system-tables like sysobjects, sysusers, ... because they follow the standard.
Propriatary systemcatalogtables may change with each fix/servicepack/version.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
try this :
exec master..xp_execresultset N'select ''GRANT ALL ON ['' + name + ''] TO test'' from sysobjects where type in (''U'', ''V'')', 'ADL'
your table / view name might contain space. I normally do not create object name with space in between
-
and owned by DBO or the current user 
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks all.....specially alzdba.....
one more thing i want to know is......
In my DBs Different USER DEFINED FUNCTIONS have different privileges....i.e
Some have SELECT
Some have EXECUTE
Some have SELECT, INSERT, UPDATE, DELETE
Some have SELECT, INSERT, UPDATE, DELETE, EXECUTE
Now i want to know is why they have different privileges....does it depends on SQL Statements used in the FUNCTIONS or some thing else.
-
Thanks all.....specially alzdba.....
one more thing i want to know is......
In my DBs Different USER DEFINED FUNCTIONS have different privileges....i.e
Some have SELECT
Some have EXECUTE
Some have SELECT, INSERT, UPDATE, DELETE
Some have SELECT, INSERT, UPDATE, DELETE, EXECUTE
Now i want to know is why they have different privileges....does it depends on SQL Statements used in the FUNCTIONS or some thing else.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply