Sql Server 2000 Login user

Question

Sql Server 2000 Login user

APATIRA LOOKMAN

SSC Journeyman

Points: 96
More actions
August 12, 2010 at 5:47 pm

#227734

Hi,
I have a database on sql server 2000 and i want to create a user for the database and also the user will be the only one that can do anything in the database so that the built-in Administrator too can not access the database without the new user, the built-in Administrator can login into the database server and work with any other database but should be able to work with mine. How can i configure and create the user for my database?
Thanks in Advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ramesh S-391202 SSC Journeyman Points: 87 More actions · Answer 1

Hi

You can remove build in administrator account and add your own windows authentication. Now no one can access your server other than you.

Thanks
S. Ramesh

www.sequalserver.com

APATIRA LOOKMAN SSC Journeyman Points: 96 More actions · Answer 2

Thanks Ramesh

I have the sa password which am currently using to login, the scenario is i want to create a user that only the user will be able to view my database while i enable built-in administrator but i don't want the built-in administrator to access my database when he logins.

i.e.

Database-One has a user created for it with username and password - appsuser

when ever the built-in admin login he won't have access to Database-One so only appsuser should be able to access Database-One when login.

crazy4sql SSCoach Points: 19590 More actions · Answer 3

so whats stopping you to do this....

if account you want in sql then create sql account else windows account and give required permission to that specific account

----------
Ashish

APATIRA LOOKMAN SSC Journeyman Points: 96 More actions · Answer 4

Thanks Ahish

I tried it but the built-in administrator can still access the database, how do i go about it?

Lowell SSC Guru Points: 323518 More actions · Answer 5

a sysadmin can't be prevented from access...that's kind of the point of the sysadmin; access to everything.

if people have sysadmin priviledges and shouldn't, then you need to remove them; then they would need a username mapped to each database they DO have access to...no username mapped...no access. simple.

well...it's simple until they find out they can't do whatever they want any more...then the fireworks start.

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!

APATIRA LOOKMAN SSC Journeyman Points: 96 More actions · Answer 6

yeah thanks Lowell

Can u describe how can achieve that?

What i did now and it has cause uproar in my work place is to deny the built-in administrator from the security folder of the database server and nobody can login and i believe this a temporary measure because they will need access to other databases but this particular DATABASE they must not be able to do any thing there.

I tried to remove built-in administrator from this database i get this message i can't remove user because its the db owner.

Lowell SSC Guru Points: 323518 More actions · Answer 7

well lets try an example;

I invite everyone to build off this example and offer better suggestions.

lets say YourDomain\bob and YourDomain\tom are users who currently are in the builtin\Administrators group, have access to everything, and really just need access to 3 specific databases, because we know they should not have access to one or more other databases.

it might be even easier to use a role that exists in windows, instead of individuals, but i wanted a clear example for you.

you are familiar with the differences between a LOGIN and a USER in the database, right? if a login is sysadmin, then it overrides any requirement to have a matching username in each database.

so lets create a role that would have the appropriate rights that bob and tom SHOULD have. [this is where YOU have to know what they need rights to!]

lets assume the following:

1. in db1 they create objects like views,tables, etc., so they need rights, but we don't want them to be db_owner, because the db_owner can DROP the database,which is often a "bad thing"

2. in db2 they need only read and write, but never create objects, and

3. in db3 they need read only rights.

4. in db4,db5 and db6 they should not be allowed to do anything.

here's a script example to show you what i mean; we would run this script to create the roles on all three databases, and then afterwards, add the users to the appropriate roles:

USE DB1

CREATE ROLE [ReallyReadOnly]

--give reader writes to this group

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReallyReadOnly]

--explicitly DENY access to writing

ALTER AUTHORIZATION ON SCHEMA::[DB_DenyDataWriter] TO [ReallyReadOnly]

GRANT EXECUTE TO [ReallyReadOnly]

--create the Role for my Dev guys

CREATE ROLE [DBAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [DBAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [DBAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [DBAdmins]

GRANT EXECUTE,ALTER TO [DBAdmins]

--create role for my normal users

CREATE ROLE [DBUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [DBUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [DBUsers]

GRANT EXECUTE TO [DBUsers]

now we can change the database connect to "DB2", run the script, and repeat for "DB3"

once the roles are in place, we want to Add YourDomain\bob and YourDomain\tom as users to each of the databases, but with different roles in each one.

USE DB1

--db1 gets admin rights

CREATE USER [YourDomain\bob] FOR LOGIN [YourDomain\bob]

EXEC sp_addrolemember N'DBAdmin', N'[YourDomain\bob]'

CREATE USER [YourDomain\tom] FOR LOGIN [YourDomain\tom]

EXEC sp_addrolemember N'DBAdmin', N'[YourDomain\tom]'

USE DB2

--db2 they can read and write, but not create objects.

CREATE USER [YourDomain\bob] FOR LOGIN [YourDomain\bob]

EXEC sp_addrolemember N'DBUsers', N'[YourDomain\bob]'

CREATE USER [YourDomain\tom] FOR LOGIN [YourDomain\tom]

EXEC sp_addrolemember N'DBUsers', N'[YourDomain\tom]'

USE DB3

--db3 is readonly

CREATE USER [YourDomain\bob] FOR LOGIN [YourDomain\bob]

EXEC sp_addrolemember N'ReallyReadOnly', N'[YourDomain\bob]'

CREATE USER [YourDomain\tom] FOR LOGIN [YourDomain\tom]

EXEC sp_addrolemember N'ReallyReadOnly', N'[YourDomain\tom]'

--because they are not added to db4,db5,db6, they can do NOTHING in them....

--once we take away the sysadmin/drop builtin\Administrators

now that those users have what we decided are the appropriate rights, i could remove them from the Windows builtin\administrators group, so they use only the rights the login gives them, or maybe drop the builtin\administrators group altogether;

i would simply remove them from that group, myself, but it depends on what's right for your agency.

maybe that gives you some ideas on how to tackle this?

APATIRA LOOKMAN (8/13/2010)
yeah thanks Lowell
Can u describe how can achieve that?
What i did now and it has cause uproar in my work place is to deny the built-in administrator from the security folder of the database server and nobody can login and i believe this a temporary measure because they will need access to other databases but this particular DATABASE they must not be able to do any thing there.
I tried to remove built-in administrator from this database i get this message i can't remove user because its the db owner.

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!

APATIRA LOOKMAN SSC Journeyman Points: 96 More actions · Answer 8

Thanks Lowell,

you are able to point me in the right direction, but my concern is my other colleagues all use the windows builtin\administrator to login because any one of them can be called upon to work on a database, so i can't remove builtin\administrator from the database server, but they must not access my database.

with your solution if i get you correctly, i should create a login with defined privilege for them. and deny access to builtin\administrator. if yes, how can deny builtin\administrator access to my own database?

Ramesh S-391202 SSC Journeyman Points: 87 More actions · Answer 9

Hi

Just remove bulit\administrator account from login.

Thanks
S. Ramesh

www.sequalserver.com

APATIRA LOOKMAN SSC Journeyman Points: 96 More actions · Answer 10

Thanks Lowell, I appreciated your effort it helped me solved it. thanks again