Posted by Erland Sommarskog on 04/08/07 21:55

DaBrain (Tapplication@gmail.com) writes:
> I am no DBA, but this is my task.
>
> I have an SQL Server 2000 Database that has an "SQL Account" that has
> execute permission on all Stored procedures. it is what was used by
> the company. This one account is used by "all workstations".
>
> I want to fix this and use Windows Accounts, and get rid of that SQL
> Account. How do I go about adding that Windows Account permission to
> all the Stored Procedures?
>
> What I want to do is to just add several windows account then go about
> removing the permission where necessary on an account by account
> basis.

First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!

Whatever, I recommend that you create a role, and then add all Windows
logins to that role:

exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'

(I may have misremembered the order of the paramerers to sp_addrolemember.)

Then you can grant access to the procedures to the role:

SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'

As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]