Post reply

Logins issue

  • December 5, 2008 at 7:20 am

    #198536

    Hi This Is ramakrishna ....

    i have a one problem on sqlserver logins..

    i taken one server like default(sa)

    i want to give permissions perticular user called login1

    he can access only user defined databases ...

    he don't want system databases like system databases(master,tempdb,msdb)..

    how to resolve this problem...

    can you help me ....plz..

    Thanks

    Ramakrishna

  • December 5, 2008 at 7:51 am

    #908310

    Huh?

    When you create a login for SQL Server you are only granting access to the server, unless you place them in a fixed server role, not to any databases. Once you have created a login you then grant that login rights to access specific databases, and then rights within the database (if done correctly you would use a database role).

    So here is you would create a login and grant it access to the AdventureWorks database:

    CREATE LOGIN AdventureWorksUser WITH PASSWORD = 'User12345' MUST_CHANGE, DEFAULT_DATABASE = AdventureWorks;

    GO

    USE AdventureWorks;

    GO

    -- create user in databasea

    CREATE USER AdventureWorksUser FOR LOGIN AdventureWorksUser

    WITH DEFAULT_SCHEMA = Production;

    GO

    -- assign user to role

    EXEC sp_addrolemember ['role_name'], 'AdventureWorks'

    OR

    -- grant specific rights

    GRANT EXECUTE ON dbo.GetContactByMiddleName

    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 2 posts - 1 through 1 (of 1 total)

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