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)  Security . . . cleaning up

Author  Topic  Jay99 468 Posts Posted - 2000-12-13 : 18:28:25 I have inherited admin duties of a database from someone else. There is one user (sa) and a ColdFusion app which performs DML as that user. I want change the ColdFusion app so that it doesn't access the database as 'sa'. So far I have: USE master GO EXEC sp_addlogin 'dbUser', 'password', 'theDatabase' GO USE theDatabase GO EXEC sp_addrole 'WebUsers' GO EXEC sp_grantdbaccess 'dbUser', 'dbUser' GO EXEC sp_addrolemember 'WebUsers', 'dbUser' GO Now I need to grant permissions on all of the non-system objects . . . I can use: SELECT N'GRANT SELECT,INSERT,UPDATE,DELETE ON ' + name + N' TO WebUsers' FROM SYSOBJECTS WHERE xtype = 'U' to generate a script to grant permisions on the all of the User Tables. but, I am having trouble with the script to generate the script to grant execute on only the user stored procedures. This: SELECT N'GRANT EXECUTE ON ' + name + N' TO WebUsers' FROM SYSOBJECTS WHERE xtype = 'P' grants execute on the User and System stored procedures. So the question is . . . how do I re-write this last query to generate the grant execute script for only the user stored procedures. (sorry, there is no neat naming convention on the sp's that would make this easy) TIA, Jay