Post reply

How to Enfore Security

  • September 23, 2008 at 5:04 am

    #213005

    Hi All

    I just moved some servers from 2000 to 2005, when i looked on to security side there are many Schemas, I am assuming that they are from 2000 since they create a default schema for each login. Can i delete those schemas???

    Other Question is that i need to Implement security to the databases, Currently all the Logins that are been used by applications have a public role and sysadmin role, For the first step i have created sperate usernames for each applications and its on the implementation stage. The next stage is that i need to create a seperate schema that is assinged only to certain databases, how can i do this??

    All the users use windows Authentication. I need to create a schema to them, now they are assinged to default schema(dbo). How do i change this??? i need to change this in order to track changes that are been made by users to databases and tables. by this way i can easy to say that there is a change has been made. is there any other way to do this( i read about DDL Triggers, will this help???)

    Will Creating several Schemas affect Performance??

    Please keep posting your suggestions.

    Thanks for all your help in advance 🙂

    Cheers

    🙂

  • September 23, 2008 at 8:44 am

    #875740

    First let's take it slowly, you do not want to make multiple changes at once because if you have a problem which change created it?

    I believe you can remove the schemas, but first I would assign each of the users to have a default schema of dbo. You can do this in SSMS by right-clicking on the user and selecting Properties and then typing dbo in the default schema. Then you delete the schemas as long as they do not own any objects in the database.

    In your next 2 paragraphs you contradict yourself. Do the applications use windows authentication or a SQL User unique to the application?

    As far as schemas go, you can create the same schema in multiple databases because a schema it tied to a database, not to the server. Once you have schemas you need to put objects into the schema. So if all your objects are in the dbo schema you would need to move objects to the new schema. Schemas should not have a performance impact.

    DDL triggers are used to track changes made to database objects not the data itself, so if you want ot track that joe_user added a columnB to tableA then DDL triggers would be useful, but if you want to know that joe_user update an employee's last name you would need to use DML triggers.

    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

  • September 23, 2008 at 9:02 am

    #875769

    Thanks Jack

    All the applications use SQL Logins, So i am planning to get one Schema to each Application. But from where do i start, is there any nice article for this, or please advice me how to create a schema and then how to assign users to that schema.

    and the other thing, is that can i change the windows authentication to use a schema instead of default ones

    Cheers

    🙂

  • September 23, 2008 at 9:10 am

    #875786

    Unless you want to break out the objects in the database by schema, like production, sales, hr, etc... and use that to manage security you do not need to create a schema in your databases. You just have the users default schema be dbo.

    Best practices, and this has changed some with schemas, is to create roles, grant permissions to the roles and then assign users to 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

  • September 24, 2008 at 4:29 am

    #876210

    Thanks Jack

    I dont want to break applications, thats the resaon i have granted logins to all applications and its been with default dbo.

    The reason i need to create a schema is that its easy to maintain than the groups and its on SQL, i know that the initial planning and implementing will be hard. so thats the reason i need help from the experts.

    Is there any article where i can learn to create schema on 2005??

    Cheers

    Sujith

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply