SQL Server Performance Forum – Threads Archive
Sql Server 2005 admin questionsHi! Im not a very experienced SQL Server 2005 Dba, and not I got a really big job on my hand. Im pretty sure how to proceed with most of the stuff, but there are a few things that I need some insight on. We are setting up a SS 2005 server that initially will hold 3 large databases which I have successfully migrated from SS 2000. The first issue is the security. In the initial phase I have 150 users in an excel sheet that will need access to the server, all with different initial databases. The users are currently located in our active directory, hence I will be using windows authentication. In order to create SS 2005 users I created a temptable where I imported the users from the excel sheet. Then I created an SP which basically performed CREATE LOGIN ‘mydomainmyuser’ FROM WINDOWS WITH DEFAULT_DATABASE=Oneofthedatabases. Now I have all the users in my SS 2005 installation. Next is the job of assigning roles and permissions. Basically there are two roles except myself the sysadmin; developers and viewers. Developers are people that need to create and execute procedures and such, whereas viewers are people from the company that ONLY do selects on certain tables. Now things start to get a little bit interesting. I dont think I can use any of the predefined roles here. For example, a CEO only need tables 1-4 in database A, and another executive need tables 6-9 in database A and tables 1-3 in database B. And the developers usually want to be able to see all tables, plus the ability to create SPs and stuff. But this depends on what project the developers are currently working on. Now Im thinking about creating schemas, one schema for each user. And then I add whatever tables and procedures they need to their schema. This way I separate the user from the objects and get a more tier like approach? What do you think? Is this a good idea? Im worried about creating complex trees of permissions that will be a nightmare to administrate where orphaned objects will haunt me in my dreams… your thoughts so far? /Henrik
http://www.sql-server-performance.com/da_authorization_2005.asp Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Interesting article… I have read about schemas in my book from Microsoft Press too. So if I understand things correctly, the best thing to do is to create for each user a schema, and then for user "domainusername" grant the objects this user need to "his" schema. That way I get a tiered solution like this: Level 1: AD-USER (Created in Windows AD) Level 2: SQL SERVER USER (CREATE USER domainuser FROM WINDOWS) Level 3: USER SCHEMA (CREATE SCHEMA with same name as the user, what authorization?) Level 4: DATABASE OBJECT (SP, TABLE, VIEW, other schemas ETC) Totally independent Is this a good model? I feel like the concept of ROLE is somewhat lost here… Can I grant one schema to another schema? For example, the COMMON schema has tables most developers need. Therefor I want all developers to be granted the content of this schema. The schema contain tables and SP, and I guess when I grant a user this SCHEMA he/she get a copy of all the objects in the COMMON schema? The original is still owned by COMMON? For example a developer writes delete from COUNTRIES; <— Affects the countries table in the users schema(copy from original) delete from COMMON.COUNTRIES <— Illegal because the user dont have access to this? Correct?
Henrik – trying to understand schemas