February 9, 2009 at 2:21 pm
Hello,
I have a situation that i am sure i DONT know the proper answer so i have come here...yet again to get some ideas and suggestions hopefully 🙂
Scenerio... i have 75+ MSSQL 2000 servers and approx 20+ users per server. (no servers are aware of the other and the users are only on one server). Can i create a defined role that will give the users the capability to do pretty much everything they want EXCEPT create or drop databases? If the answer is yes.. please explain because i truely do not know how to make this happen.
Thanks for your time and i look forward to any assistence that coming my direction. 🙂
-Darryl
DHeath
February 9, 2009 at 3:15 pm
How many user databases on each server? Do the users really NEED this level of permissions? I think this would violate least permissions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2009 at 3:26 pm
Thanks for the reply..much appreciated
How many databases per server...ranges from 15 - 60 on the conservative side. Well i want to allow the users to do what is needed(some developer, some application users, etc) not quite "sa" but if i have created databases for them they can be added to the database as a user and i dont have to worry about them creating or dropping the databases. Most users have "read-only" for the most part but its the other 10% that are driving me crazy. Hope this helps
-D
DHeath
February 11, 2009 at 6:17 am
You would need to create a role or roles in the each database. For the situation you describe I would have a Developers role, and Application Users role and a ReadOnly Users role and assigne the appropriate permissions to each role.
-- create role
Exec sp_addrole @rolename = 'Developers', @owner = 'dbo'
-- add persmissions to the role
Grant Exec on ALL to Developers
-- add login (SQL or Windows, can be a windows group as well)
Exec sp_addrolemember @rolename = 'Developers', @membername = 'Domain\JoeDeveloper'
You can check out these links for more details:
Also, you can "nest" roles so you could create the Developers role and make it part of one of the fixed database roles
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 11, 2009 at 9:28 am
Thanks for the insight.. much appreciated.
-Dheath
DHeath
February 20, 2009 at 8:34 am
Ok..so far so good... but now i would like to know this.... please say its true 🙂 but am i able to make this one role server wide so that it doesnt have to be just for that one database but i can add databases to this role as the server grows. you know in the same sense as the oradba role for oracle.
thanks
-D-
DHeath
February 20, 2009 at 8:43 am
Sorry, you can't create server level roles.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply