Convert SQL Account to Windows Accounts + EXEC on ALL SPs

[DaBrain]

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.

Any suggestions would be greatly appreciated!

[Erland Sommarskog]

DaBrain (Ta**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DickChristoph]

Hi

DaBrain, will also have to configure all the client applications to use
Windows authentication instead of SQL Server Authentication. (or instruct
the users as to how to do it).

Depending on the number of users, the application they are using, this might
be a time-c0nsuming task.

If they connect through Access XP ADPs this is a relatively simple task.

If they connect using a DSN it will be slightly more difficult.

If they connect through some custom application where the connection info is
stored in a .INI file or the registry and the developers didn't plan on
connecting with Windows authentication this might be somewhere between
difficult and impossible.

--
-Dick Christoph

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...

DaBrain (Ta**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx