August 2, 2009 at 5:35 pm
I am planing to automate some of my regular stuff which really saves a lot of time to me.
Every day i check a website where in i look for details as to what users have to be given access(read/write.ddl_admin) to what databases. Everytime i do this i have to drop existing users(not all users i have to keep couple of them) in the DB and assign new users from the web report.
1. I dont think i can automate 1st part to as to getting user list from the web report.
2. secondly, I wud like to create a sproc to map users to the database passing dbname and users as parameters.
Any idea to improve on this?
August 2, 2009 at 6:25 pm
1st step:
Here is the list of users i am dropping..
SELECT * FROM sys.database_principals
where name not in (select name from model.sys.database_principals)
but how do i drop them all at once?
2nd step:
Add multiple users to the same database at once, if i add one after another then 1st step will be executed and drops the existing one and so i want to add them all at once, how do i do this?
Thats what i am thinking of , does it look silly? any ideas?
August 3, 2009 at 9:37 am
any suggestions?
August 3, 2009 at 9:59 am
you can't drop them all at once. You can loop across the result set (while or cursor) and issue a DROP USER for each one.
The same thing occurs for the new users. You need to add them with a CREATE USER individually. I'd say that you need a script to do this, though I'm not sure how you are getting the list. If you have to type each name, then you might load them into a table of some sort and loop through there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply