Click here to Skip to main content
15,064,166 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello friends...
i have created website for intranet and hosted it on server. for that i needed to create login "IIS APPPOOL\hi" in sql server 2008 for my application to access my "reportdb" database. "IIS APPPOOL\hi" has sysadmin and public server roles in sql server 2008. And i had created another login "sa" same as "IIS APPPOOL\hi". these are working correctly.
Now I want these two logins to access "reportdb" for all operations in database and remaining all logins should be denied to access "reportdb". My Sql Server 2008 is having mixed mode (windows authentication and Sql authentication). plz help me

1 solution

You can either go to the Database => Security => Users => New User menu or Security => Logins => New Login menu to add users and assign them permissions on the database. You can assertively deny permission for roles/users on any object and operation.

Please note, I'd advise generally to use the datareader and/or datawriter roles, although these do not apply to stored procedures etc. so you'd have to manage access to them manually or create a role. I would NEVER give a web access account SA privileges. I might apply dbowner to an account to debug for permission problems, but only until I'd figured out the missing permission. If I want to exclude some object, I'd also apply a specific deny permission. If I only wanted access to a few objects from many, I'd not use the in-built roles, although I might use a custom DB role or AD group.

You can also script database permissions, but the GUI is quicker and easier. Scripting is usually reserved for remore deployments.

Please note that if you use integrated (Windows AD) security, the user must be in the same or a trusted domain as the DB server.
TAUSEEF KALDANE 28-Aug-14 2:46am
thank you sir

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900