January 16, 2009 at 9:19 am
I want to pick everyone's brains and see if I'm doing this the best way.
We are moving items over to SQL 2005 from SQL 2000.
Once I restore the database over onto 2005 it's time to clean things up. For these databases, there's a SQL Login that hits it.
Let's call it ApplicationUser for example.
There's an ApplicationUser login for the server, and an ApplicationUser User in each specified database.
I want to give the ApplicationUser execute permissions on the stored procedures needed within each database.
I've been doing this by going into the securables on the ApplicationUser User within the database and giving them execute on the necessary stored procedures.
I just wanted to know if this was the best or only way to do this.
Because of our setup the schema has to remain as dbo. Application Roles are out as well. I don't want to create a new Database Role.
Thoughts?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
January 16, 2009 at 9:43 am
I know you said you don't want to use a Database Role, but I think that's the easiest way.
Are you granting execute rights on ALL stored procedures or specific stored procedures? If all you could write a script that iterates over the sp's and runs the grant's. Something like:
DECLARE @proc_name sysname
DECLARE cProcs CURSOR FAST_FORWARD For
SELECT
routine_name
FROM
INFORMATION_SCHEMA.ROutines
WHERE
routine_type = 'Procedure'
OPEN cProcs
FETCH NEXT FROM cProcs INTO
@proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(1000)
SET @sql = 'Grant Exec on ' + @proc_name + ' to user'
PRINT @sql
-- EXEC(@sql)
FETCH NEXT FROM cProcs INTO
@proc_name
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 9:44 am
You can use this script to generate Grant permission for each procedure to the application user.
SELECT 'GRANT EXECUTE ON [dbo].['+ ROUTINE_NAME + '] to ApllicationUser'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
and ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_CATALOG = ' '
-Roy
January 16, 2009 at 9:57 am
not all of them.
I've gotten them to standardize and use usp_ instead of sp_. I would say probably all of the user stored procedures (I hope there all usp_) and not the system stored procs.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
January 16, 2009 at 10:07 am
Thanks, I tried this way and it works:
SELECT 'GRANT EXECUTE ON [dbo].['+ ROUTINE_NAME + '] to ApllicationUser'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
and ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_NAME LIKE '%usp_%'
Saves me a good deal of time. I will look at the other way as well.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
January 16, 2009 at 11:50 am
Look into the db_executor role (search on SSC or Google)
Idea is you create a Database Role
Assign GRANT EXECUTE to that role on all stored procedures (possibly with a TRIGGER so any newly created SP won't need manual GRANT EXECUTE)
Assign users/logins to that Role, now they can execute the SP
We use it here
-- CREATE A NEW ROLE
CREATE ROLE db_executor
-- GRANT EXECUTE TO THE ROLE
GRANT EXECUTE TO db_executor
This is code to find all proces that don't have db_executor role
SELECT USER_NAME(so.uid) Owner, so.[name] StoredProcedure
FROM sysobjects so
LEFT JOIN
(SELECT [id]
FROM sysprotects
WHERE uid = USER_ID('db_executor')
AND [action] = 224) sp
ON so.[id] = sp.[id]
WHERE so.xtype = 'P'
AND sp.[id] IS NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply