DB from 2000 to 2003 Server - SQL Server Forums

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)

DB from 2000 to 2003 Server

Author

Topic

ccomstock2
Starting Member

18 Posts

Posted - 2005-01-07 : 10:34:21

I am in the process of switching our web server for our Windows 2000 server to a new Windows 2003 server. I have been able to copy the database over with no problem. The problem I have is all of the stored procedures have permissions for a user that is not on the new system. Also, in the prior set up there was just permission set for the anonymous access accout not a group. Would it make more sense to have a group and assign the group permissions so I don't have this problem in the future? Also, how would I go about changing the permissions for all the sprocs, is there a statement or system sproc? Thanks.

The DB is SQL Server 2000.

ccomstock2
Starting Member

18 Posts

Posted - 2005-01-07 : 11:50:44

I found a solution for what I was looking for:

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) = 'sp'

Run this script in the Query Analyzer, then copy the resulting rows and pasted them in QA and exectue. It will change all the sprocs permissions for the user.

Kristen
Test

22859 Posts

Posted - 2005-01-08 : 07:03:51

You ought to use the name of a ROLE in place of "MyLogin" above

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-08 : 12:47:03

To expand on what Kristen said. Assigning permissions only to roles prevents issues just like this. In addition, you can then add/subtract people from those roles without affecting the server. It's more portable, more expandable, and the right way to handle permissions. If you want to go a step futher:

AD User>AD Group>SQL Server Role>EXEC permissions>Table Access only through procedures.

Secure, portable, seperation of duties in management.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources