
January 15th, 2008, 08:38 AM
|
|
Registered User
|
|
Join Date: Jan 2008
Location: Connecticut, USA
Posts: 15
Time spent in forums: 5 h 7 m 11 sec
Reputation Power: 0
|
|
|
Scripting SQL Server Permissions
I have a scenario that I want to streamline by using a SQL Script.
First, som background:
==================
We have a development environment and a production environment running ASP.NET and a MS SQL Server.
The ASP.NET uses a login (MyLogin) to access the SQL Server.
The Login is part of the group (MyGroup) which has access to execute the stored procedures and be a datareader.
The two environments have everything the same except for the server name (TheProd and TheDev ). This way the virtual directories (MyVirtualDirectory) and database names (MyDb) are the same (TheDev/MyVirtualDirectory and TheProd/MyVirtualDirectory both access a database called MyDb located at TheDev/MyDb and TheProd/MyDb).
The Problem:
==================
Whenever I restore the Ms SQL Database 2000 (MyDb) the login doesn't connect to the group properly and cannot access anything that the group has permissions even though it appears to be part of the group.
I have to remove the user (MyLogin) from the Database and then in Enterprise Manager go into Security and add that person back into the database (public, datareader and MyGroup).
The Request:
==================
How can I delete the user (MyLogin) from the database (MyDb) and then add the user to the group (MyGroup)?
It is not really effective to set the permissions for each table, view and stored procedure seperately and with the restoration the Group holds the permissions just fine.
GRANT and DENY seems to only work with connecting a login to the table/view/stored procedure individually and not the Group.
Thanks!
|