January 21, 2008 at 1:38 am
Hi,
I am developping small application within my firm and therefore I use SQL Server 2005 as DB
Now I would like to limit the security based on the users windows logon.
But mostly there are multiple users that should have access to those tables.
Is there a possibility to add multiple users to a group, and give access rights to that group.
I know this can be done by creating AD groups and creating a SQL login based on this group, but I would like to do this in SQL to keep it simple.
Thanks!
January 21, 2008 at 1:45 am
Hi
If i have understood your question correctly then here goes...
You can created SQL logins from the windows logins that you have, create a group consisting of these logins. This group can be given specific rights.
"Keep Trying"
January 21, 2008 at 1:58 am
Can you create SQL Groups? how?
I know I can use AD Groups and create a SQL Login, but I would like to keep the usersmanagement of the SQL on the SQL Server.
January 21, 2008 at 4:24 am
just grant de AD-group login rights to your sqlserver instance, then add the ad-group to the correct database role and grant the needed privileges to the AD-group.
use yourdb
GO
-- Windows auth
if not exists (select * from master.dbo.syslogins where loginname = N'YOURDOMAIN\your windows groupT')
exec sp_grantlogin N'YOURDOMAIN\your windows groupT'
exec sp_defaultdb N'YOURDOMAIN\your windows groupT', N'master'
exec sp_defaultlanguage N'YOURDOMAIN\your windows groupT', N'us_english'
GO
if not exists (select * from dbo.sysusers where name = N'your windows groupT')
EXEC sp_grantdbaccess N'YOURDOMAIN\your windows groupT', N'your windows groupT'
GO
exec sp_addrolemember N'db_datareader', N'your windows groupT'
go
GRANT EXECUTE ON [dbo].[spc_whatever] TO [your windows groupT]
GO
Keep in mind that the biggest downside of working with windows
authentication is that you have no control over the medium that your
user uses to get to your database.
What I mean is that there is no guarantee that your application is the
only means that a user can utilize to get to your data.
(especialy if they also have e.g. MS excel or MS access (linked tables),...)
With sql2005 SP2 you can offcourse implement server login triggers and
deny all useraccess base on "application name" info, but that might be
a bit to far ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2008 at 12:24 pm
You can create database roles and grant all SQL rights to the roles, or give the new roles membership in db_datareader, db_datawriter, etc. Then you can add each Windows login user to the appropriate role(s) to control their access without having to manage object-level permisions user-by-user. Note that this has to be repeated in every database.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply