September 7, 2009 at 1:43 am
Hi all,
i have installed and configured Sqlserver 2005. After that i restored a database in that sever. here the problem came into picture.
1) I didn't gave any access to any user.But using windows auth any one can conn to the server with out any user credentials in the server.but they can not able to access the databases. how can they conn to the server with out user credentials in the server.
2) I had restored a database into this server.I dint gave Database access permissions to any USERS. but the users in the server can able to access the database with out any db permissions.
How these above will be possible with out proper credentials??
can any one guide me on this...........
Thanks in advance
Regards,
ROCK. 🙂
September 7, 2009 at 3:14 am
Rock, instead of concentrating on not granting access to any users, did you check that maybe youdomain\everyone
or an "all users in domain" group have been granted access?
sounds like there is a domain group involved here which grants any AD authenticated user access to connect, although nothing has been configured on user level on any db.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 7, 2009 at 8:33 am
hi
i got it
Thank you very much for the reply.
September 7, 2009 at 9:21 am
hi all,
for my second question, i used the below statement
USE MASTER;
GO
DENY VIEW ANY DATABASE TO PUBLIC
it restricts the public users to access the DB.
here i am getting one problem. The database owner also can't able to view a specific database.
how can i grant the user to view specific databases only????
how can i deny the users to view specific databases only???
is there any way to grant a view permission to specific database for one user.
EX: GRANT VIEW TEST DATABASE TO TESTUSER
TEST IS THE DATABASE NAME
TESTUSER IS THE USERNAME
can any one suggest me on this
Thanks in adv
ROCK...
September 7, 2009 at 12:43 pm
Grant view to testuser will grant view permission on specific view. if you need the user to view all tables, then add him db_datareader role
September 7, 2009 at 11:32 pm
i want to know how to give view db access to a user on his specific database.
ex: In SSMS, If one user has a db owner permission to one database.he can able to view all the databases in the server. i want to restrict him to view only his specific database.
2) Yesterday i exec the below query in SSMS. public users can't view all the databases in the server.But the db owner of one database also will not able to view his DB.I know every user is under public role.
DENY VIEW ANY DATABASE TO PUBLIC
Today morning i am getting one error:
login failed for user testuser error:18456
any idea???
TX
ROCK...
September 8, 2009 at 1:26 am
rockingadmin (9/7/2009)
i want to know how to give view db access to a user on his specific database.ex: In SSMS, If one user has a db owner permission to one database.he can able to view all the databases in the server. i want to restrict him to view only his specific database.
2) Yesterday i exec the below query in SSMS. public users can't view all the databases in the server.But the db owner of one database also will not able to view his DB.I know every user is under public role.
DENY VIEW ANY DATABASE TO PUBLIC
Today morning i am getting one error:
login failed for user testuser error:18456
any idea???
TX
ROCK...
Not sure what you are trying to achieve here. following on what you have said. denying access to public.
db_owner is a database role and is specific to that database. it does not give rights to any other database.
to give specific rights to users. if their login/group already exists, create them as a user in the database that you want them to have access to. then you can decide what rights that you want them to have and then can apply the relevant database roles, to give either select/update/delete/alter/execute permissions etc.
if you want a specific user to be able to do everything in their database, and i am assuming they have a windows domain account. create a login based on their windows account and add them in as a user for their database. grant them the db_owner role, if you want to restrict what they can do, use the db_datareader or db_denydatawriter roles.
db_owner
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin
Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin
Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator
Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader
Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter
Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader
Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database
concerning your failed login, i am assuming it is a sql user. change the password and check that you can login in yourself, using that login. if you need any more help, just post.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 5:57 am
i changed the password using sysadmin user.
after that i tried to connect with normal user, same error occurred
only sysadmin can able to login.other users can't able to connect to the server.
i found the reason for this in the error log:
Error: 18456, Severity: 14, State: 12.
any idea about this??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply