Security

Question

Security

Lee From Bangalore

Right there with Babe

Points: 752
More actions
May 4, 2009 at 2:39 am

#208954

In Windows server few users are administrators like user1,user2,user3.
but in sql server(Windows Authenticaon) access, I need to give few roles only to those users, How can I Give?

Viewing 15 posts - 1 through 15 (of 18 total)

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

Dugi SSCoach Points: 17998 More actions · Answer 1

Lee From Bangalore (5/4/2009)
In my server few users are administrators like user1,user2,user3.
but in sql server access, I need to give few roles only to those users, How can I Give?

What is your server ?

You can create users in SQL Server and give them permissions whatever you want !!!

Be more specific what do you want to do!

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Lee From Bangalore Right there with Babe Points: 752 More actions · Answer 2

Hi Dugi

I mean

In windows server the users(admin rights) are rights to access sql server, right

But I need to give few roles only to access sql server(Windows authenticaion) to those users. How can I give ?

Dugi SSCoach Points: 17998 More actions · Answer 3

Lee From Bangalore (5/4/2009)
Hi Dugi
I mean
In windows server the users(admin rights) are rights to access sql server, right
But I need to give few roles only to access sql server(Windows authenticaion) to those users. How can I give ?

Open SQL Server SSMS and expand Security-Logins ...you will see the windows logins if you add them before! After that with right click go to the properties of every user and you can give them server roles or you can do user mapping to any database that you have in you SQL Server!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Lee From Bangalore Right there with Babe Points: 752 More actions · Answer 4

Thanks Dugi

the users are administrator in Operating system

In sql server also they have Sysadmin.

But in sql server I dont want to give the sysadmin role to those users. In sql server we can't remove sysadmin role to those users bez they are windows administrators.

Dugi SSCoach Points: 17998 More actions · Answer 5

Dugi (5/4/2009)

Lee From Bangalore (5/4/2009)
In my server few users are administrators like user1,user2,user3.
but in sql server access, I need to give few roles only to those users, How can I Give?

You can see more information about fixed DB roles in SQL Server...here[/url]

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 6

Lee From Bangalore (5/4/2009)
Thanks Dugi
the users are administrator in Operating system
In sql server also they have Sysadmin.
But in sql server I dont want to give the sysadmin role to those users. In sql server we can't remove sysadmin role to those users bez they are windows administrators.

So what do you want for these users do you want just read data or deny for log in in server or you can disable these users to connect in SQL Server etc etc you can have many options to administer SQL Server logins (with mix authentication, Windows- Domain\User logins and SQL Server logins!)

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ben.rosato SSCommitted Points: 1929 More actions · Answer 7

Lee From Bangalore (5/4/2009)
Thanks Dugi
the users are administrator in Operating system
In sql server also they have Sysadmin.
But in sql server I dont want to give the sysadmin role to those users. In sql server we can't remove sysadmin role to those users bez they are windows administrators.

Remove BUILTIN\Administrators from Sql server logins.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

Before you remove the BuildIn\Administrator group , make sure you add your Windows user as SysAdmin as back up. Or else if by any chance you forget the password of SA, you are locked out of your DB Server.

Be very very careful when you remove it.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

I for got to mention that after you have removed the BuildIn Administrator role (Or you can leave the BuildIn/Administrator group as it is but just go to the properties and remove the SysAdmin role) you create a new role with the access you want to give the user1, user2 user3 and then add these users to this role.

-Roy

Lee From Bangalore Right there with Babe Points: 752 More actions · Answer 10

Hi Roy,

Thanks for your reply.

If I remove 'SysAdmin' role in Bulit\Administrator Group,

Will automatically removed Check box in 'Sysadmin' role to that windows authenticated users?

Pls Let me know

With Regards

Lee

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

Yes, it should unless you have added those users separately and given them rights.

Please make sure that you add your Windows login separately and add your user as Sysadmin.

-Roy

Lee From Bangalore Right there with Babe Points: 752 More actions · Answer 12

Hi RoY,

With out removing the 'Sysadmin' role for BUILTIN\Administatrors' group

Can I Create Group in Local system and assign the users and gave the permissions to the group in SQL Server?

With Regards

Lee

ben.rosato SSCommitted Points: 1929 More actions · Answer 13

Lee From Bangalore (5/10/2009)
Hi RoY,
With out removing the 'Sysadmin' role for BUILTIN\Administatrors' group
Can I Create Group in Local system and assign the users and gave the permissions to the group in SQL Server?
With Regards
Lee

Yes. That will work. An active directory group would be even better.

Lee From Bangalore Right there with Babe Points: 752 More actions · Answer 14

Hi,

If I remove sysadmin role in Builtin\administrators group, is there any problems will occur( like services,,,)?

With regards,

Lee