Post reply

Need to setup a login that limits the user to one db only.

  • January 27, 2011 at 7:00 pm

    #232896

    I need to setup a database role that allows the users to have access to all objects within the database except security. We have several databases in the sql instance. I only want them to be able to login and see anything within that one db. I am new to sql security and can't seem to get this figured out. I need them to have full rights to tables, views, stored procedures, etc. I do not want them to be able to view security or the master, temp db, etc. I hope I made sense.

    Thanks

  • January 28, 2011 at 6:38 am

    #1279667

    Such a role already exists: db_owner. Basically, you need to do the following:

    1. create a login for the user(s) to log in with. Recommend Windows integrated security if possible.

    2. create a user in the specific database they are to have access to linked to the login you created.

    3. add this user to the db_owner database role which will give it access to all objects in the database.

    if you need anything more sophisticated than this (doesn't sound like you do) you will need to creat a new database role and give it specific permissions and/or add it to other roles as required.

    The probability of survival is inversely proportional to the angle of arrival.

  • January 28, 2011 at 8:55 am

    #1279747

    I have them in the role right now as db_owner. But they have access to the security in that database and also in the Master, Tempdb, and distribution database. I want them to only have access to tables, views, synonyms, programmability. That should be it. Currently they are setup in the instance with the public role - which does give him access to the master, tempdb and distribution db. I gave them select rights to the db i want them to have. In that db they have their own schema and are a member of the db_owner role. Any other suggestions?

    Thanks!

  • January 28, 2011 at 10:51 am

    #1279834

    You have other issues then because a user on one database does not automatically have access to other databases unless they are sysadmin, where they have access to everything. That sounds like what is happening in your case.

    You need to look at the SQl server login you are using (login is not the same as a user in a database) and make sure it is not a member of sysadmin database role, or member of a windows group that has sysadmin rights, or access to the other databases.

    The probability of survival is inversely proportional to the angle of arrival.

  • January 28, 2011 at 12:58 pm

    #1279899

    i was under the impression that simple CONNECT rights gives you access to creating objects in tempdb, and the same for at least reading tables in the master database(sys.objects, etc).

    to prevent the access to master, wouldn't you create a role in master, and DENY VIEW DEFINITION to [thatrole]

    what is wrong with access to tempdb? AFAIK they can only see what they created there in there session....

    (as a proof of concept) i create one login right now, switch to that context, and can easily see sys.objects and create a temp table....

    EXEC master.dbo.sp_addlogin @loginame = N'Noobie', @passwd = 'NotARealPassword', @defdb = N'master', @deflanguage = N'us_english'

    EXECUTE AS LOGIN = 'Noobie'

    --confirm my context ahs changed: who am i?

    SELECT suser_name()

    select * from sys.objects --works!

    SELECT *

    INTO #TEMP

    from sys.objects

    SELECT * FROM #TEMP

    --turn back into myself

    REVERT;

    if it's that important, i think you have to create a user for the login in master, maybe a role as well, deny view definition and deny view any database, and then tie the user to teh role.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 28, 2011 at 1:20 pm

    #1279924

    creating a temp table like; select * into #temptable is not the same as:

    create table tempdb.dbo.temptable ( column1 int )

    You cannot select from sysobjects in master database unless you are a user in master (or a member of a server role that gives you access to master like sysadmin).

    The probability of survival is inversely proportional to the angle of arrival.

  • January 28, 2011 at 7:34 pm

    #1280022

    I guess I need to do some more research on this.

    I created a login in the instance with the public role (I think that role is my problem) then added the user to the db_owner role in the database I want them to have access to. I also gave them their own schema. It works except they can change the users, or roles and I don't want them to be able to do that and they also have rights to the master table and security. Does that mean I need to create a role that deny's access to those objects and add them to it? This works so much differently than sql 2000 that I feel rather lost.

    Thanks, I got some material to read on the security. Guess I'll get busy reading.

  • January 28, 2011 at 11:47 pm

    #1280044

    have you tried setting the user's default database to the user database.

    if they have access to modify other database then they could of inherited perm from another group.

    -----------------------------
    www.cbtr.net
    .: SQL Backup Admin Tool[/url] :.

  • February 11, 2011 at 5:53 pm

    #1285971

    I think it is OK the way I set it up. It looks like they can do more than what they can. I have them setup as db_owner in the db I created for them. Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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