set password for particular db

Question

Post reply

set password for particular db

jangidsatish148

SSC Rookie

Points: 36
More actions
November 14, 2010 at 8:55 am

#241092

how to set password for particular db in sa login

Viewing 9 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323743 More actions · Answer 1

jangidsatish148 (11/14/2010)
how to set password for particular db in sa login

you'll want to revamp how you are currently doing access;

first, to be clear, a login has one password only. you cannot change it for each database.

based on your question, i can tell you are using the sa login for everything, and that's not the best practice. best practice is to change the "sa" password to something noone knows,no longer permiut people to use the sa password, and create multiple logins and roles to control access on a per-database level. then you add users to those roles.

create database Sandbox

GO

USE Sandbox

--create a role which can READ any table, and execute any procs/functions

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 [SandboxDEVAdmins]

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

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

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

GRANT EXECUTE,ALTER TO [SandboxDEVAdmins]

--create role for my normal users

CREATE ROLE [SandboxDEVUsers]

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

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

GRANT EXECUTE TO [SandboxDEVUsers]

--now add specific users to nearly-Admins

IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

BEGIN

EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'Sandbox', @deflanguage = N'us_english'

END

IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')

BEGIN

EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'Sandbox', @deflanguage = N'us_english'

END

USE [Sandbox]

--make a user in the db for the matching login

CREATE USER [bob] FOR LOGIN [bob]

CREATE USER [jeff] FOR LOGIN [jeff]

--add these logins to the role

EXEC sp_addrolemember N'SandboxDEVAdmins', N'bob'

EXEC sp_addrolemember N'SandboxDEVAdmins', N'jeff'

--create logins for any domain users

CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[Sandbox]

CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]

--any one logged into the domain can have basic read/write access via their login

EXEC sp_addrolemember N'SandboxDEVUsers', N'NT AUTHORITY\Authenticated Users'

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!

jangidsatish148 SSC Rookie Points: 36 More actions · Answer 2

there are many database of many people. so anyone open database using sa login. now i want no one should be able to open my database except me.

how to go about that?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

If everyone's using the sa account, then everyone has access to everything. Login names (and passwords) are server-level, not database level, and anyone who's a member of sysadmin (including sa) has complete rights to everything on that SQL instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jangidsatish148 SSC Rookie Points: 36 More actions · Answer 4

how to solve this problem in best manner.

please suggest me step by step because i am completly new in sql 2005

thanks in advance

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

If everyone is using the sa login, you cannot in any way prevent them from seeing or doing what the want on any database on the server.

sa (and in fact any sysadmin login) means unrestricted rights to everything

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323743 More actions · Answer 6

like i mentioned before, the only solution is to take away the sa login from everyone, and make them login with something else.

in a place where everyone has been using sa for a long time, this can be a hard choice for some, but you can always pass the blame by saying "i was told to tighten security". once the new logins are in place, it'll be much easier to keep things tight in the future.

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!

jangidsatish148 SSC Rookie Points: 36 More actions · Answer 7

I understood your point. i will create new login under which i will keep my db but i have to handover backup copy of db to client. whenever client need to take information, he can do it by restoring this backup file.

Here i want solution of MS Access. if i set password for MSAcess file, no one can open it without my permission because i have password.

Lowell SSC Guru Points: 323743 More actions · Answer 8

SQL server is already like that...once the sysadmin restores or creates a database, NOONE has access to it, except the sysadmins.

as long as no one except the dba has sysadmin privileges, the only access is what gets specifically granted to the users...that could be no access at all (the default), grant to one specific table, or more (like my example)

on a dev machine, where everyone is logging in as sa, you are seeing everyone having access to everything, but that's (hopefully) not how a client would do things.

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!