July 30, 2002 at 8:27 am
I created a new SQL server login and gave rights as db_datareader and db_datawriter for a particular database. But the user is not able to run stored procedures in the database.
Is there a way where we can globally specify rights for the login to execute all the non system stored procs in a database (other than manually giving execute permissions for every stored procedure)?
A second question. I have a 4 gig database. We have about 650 non-system stored procs. Is that a poor design? Does it seem acceptable? I underdstand it depends on the type of the application, but this is for all those pros out there who have seen 1000s of databases. is 650 too much? Is there a better design strategy that I should be aware of?
July 30, 2002 at 8:40 am
In answer to your first question, it is probably a good idea to create a role and assign all the stored procedure execution rights to it. Then the next time you have to add a login, you simply assign the login to the role. Unfortunately, there's no automatic way of assigning rights for every stored procedure. It can be scripted, however. There might be a script in the library. What I typically do is
SELECT
'GRANT EXECUTE ON [' + name + '] TO MyRole'
FROM sysobjects
WHERE xtype='P'
And then after I ensure it's what I want, I copy and paste it and execute. Others have automated it using cursors and EXEC().
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 30, 2002 at 11:22 am
A large number doesnt mean there is a problem or require a change. If you've got multiple different procs that do the same thing that would be "bad" only in that you have to fix them all when a change is needed. The total will also depend on how many apps are using the db.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply