Sql Permissions Help ?

[littlemoon]

[quote name='Kaarthikeya' timestamp='1362592100' post='1303375260']




what he meant is there will be roles to user at the server level and database level...sometimes table level or object level lo kuda untundi AFAIK

So, meeru Server level lo aa user ki access ichi, DB level lo ivvakapothe it doesn't work ani

nenu DBA kaadu so naaku inthakante ekkuva teleedu
[/quote] nenu server level lo ivaledhu DB level lo ichanu... and my goal is to give them only DB level access not server level -- they will not login to sql
[quote name='ghazni' timestamp='1362592746' post='1303375347']
when you do a backup and restore sometimes [b]if you created permissions manually on DB level[/b] it may not restore the permissions of that user...

we have to create agian the permissions.... it happened to me this once.... i am not DBA but this practically experienced....
[/quote] I dint get this part.. manually ante ? GUI thru na?

[deals2buy]

[quote name='Guest' timestamp='1362594127' post='1303375448']
Login - used for server authentication, meaning it will let you in SQL server. This is at server level
Database User - Allows to access the database after authetication. This is at the database level
Database Role - If you want to restrict the database users permissions, you can create a role. For ex : I want only the user to execute the SPs but not to modify them etc.,

Every database user must be associated with a Login. Logins are stored in the master database and database users are stored in the database. When you restore the database, you need to transfer the logins also unless you restore the master database as well. Assuming the you have all corresponding Logins exist for the database users, after restoring their SIDs don't match, so you need run this SP sp_change_users_login 'report' to get the report and fix and update the SIDs one by one. Ex : sp_change_users_login 'update_one', 'test', 'test'.
[/quote][img]http://www.manadb.com/Smileys/default/goodpost.gif[/img] Well said
[quote name='vegeta' timestamp='1362594522' post='1303375476']
Login is server level, user is at database level. Each user in database must have a login (SQl account/windows account) associated with it.. If you restore a DB all windows logins will get re-associated with users, but SQL logins won't. they become orphan users in the database..you can fix them running

sp_change_users_login 'report'
sp_change_users_login 'auto_fix', 'user'
[/quote][img]http://www.manadb.com/Smileys/default/goodpost.gif[/img] Well said
[quote name='ghazni' timestamp='1362594659' post='1303375486']
emo naku oka db access ledu ma team mate vadiki anni permissions unayi so vadu nanu add chesadu aa db ki apudu i could access so idi manual ey kada.....
[/quote]
me teammate DBA na? Dev lo aithe atta etta add sestaru ?
[quote name='littlemoon' timestamp='1362595337' post='1303375529']
nenu server level lo ivaledhu DB level lo ichanu... and my goal is to give them only DB level access not server level -- they will not login to sql
I dint get this part.. manually ante ? GUI thru na?
[/quote]
Server ki access ki lekunda DB ki access ela istharu chepma

[littlemoon]

[quote name='loveindia' timestamp='1362592811' post='1303375357']
yes aa database lo kuda aa user ni specify cheyali... server level lo permission antey the user can access any database on that server but he needs permissions over that database again to access that particular database.. got it?

SERVER
Database -- USER
-- USER

Both should be there
[/quote] Ok, nenu only DB level permission ivali as the users will not login into sql ... apudu only DB level isthe aripothundi kada?
[quote name='Guest' timestamp='1362594127' post='1303375448']
Login - used for server authentication, meaning it will let you in SQL server. This is at server level
Database User - Allows to access the database after authetication. This is at the database level
Database Role - If you want to restrict the database users permissions, you can create a role. For ex : I want only the user to execute the SPs but not to modify them etc.,

Every database user must be associated with a Login. Logins are stored in the master database and database users are stored in the database. When you restore the database, you need to transfer the logins also unless you restore the master database as well. Assuming the you have all corresponding Logins exist for the database users, after restoring their SIDs don't match, so you need run this SP sp_change_users_login 'report' to get the report and fix and update the SIDs one by one. Ex : sp_change_users_login 'update_one', 'test', 'test'.
[/quote] Thank you challa clear ga explain chesaru... SIDs match avali DB restore chesinapude e sp run chesanu and all corresponding DB users match ayayi except 2 which were orphaned so avi malli recreate chesanu ...

I dint restore master DB so may be that is the reason sql level logins and DB level users different unayi..


[b]Okasari confirming if I understood right point mee andarii posts tho[/b] - DB varuke users ki permission isthe saripothundi if they will not login to sql server ... anthe kada
[size=4]Sql level logins kuda kavali ante master DB ni restore cheyali anthe na? [/size]

[littlemoon]

[quote name='Kaarthikeya' timestamp='1362595750' post='1303375587']
Server ki access ki lekunda DB ki access ela istharu chepma
[/quote]

3rd party tool use chestharu where they point out to DB instance name and their account

so sql server lo ki em login avaru

[deals2buy]

[quote name='littlemoon' timestamp='1362596122' post='1303375654']

3rd party tool use chestharu where they point out to DB instance name and there account

so sql server lo ki em login avaru
[/quote]
its consfusing

Server -> Database -> Object

This is how it works kadaa... Root level lo permission lenappudu sub roots lo ela access cheyyagalugaturu anedi naa doubt..no matter if they use third party or fourth party tool

[littlemoon]

[quote name='vegeta' timestamp='1362594522' post='1303375476']
Login is server level, user is at database level. Each user in database must have a login (SQl account/windows account) associated with it.. If you restore a DB all windows logins will get re-associated with users, but SQL logins won't. they become orphan users in the database..you can fix them running

sp_change_users_login 'report'
sp_change_users_login 'auto_fix', 'user'
[/quote]
already exec chesanu sp... but meeru run cheymantundi per each database a ? or master lo run cheymantunara?

[littlemoon]

[quote name='Kaarthikeya' timestamp='1362596328' post='1303375689']
its consfusing

Server -> Database -> Object

This is how it works kadaa... Root level lo permission lenappudu sub roots lo ela access cheyyagalugaturu anedi naa doubt..no matter if they use third party or fourth party tool
[/quote]
root level ante sql level a DB level a ?

[deals2buy]

[quote name='littlemoon' timestamp='1362596471' post='1303375711']
root level ante sql level a DB level a ?
[/quote]
sql level ante ento naaku ardham kaaledu but nenu adigedi Server ki access lekunda andulo DBs ki access ela vastundi ani naa doubt

[Guest]

[quote name='littlemoon' timestamp='1362596057' post='1303375639']
Ok, nenu only DB level permission ivali as the users will not login into sql ... apudu only DB level isthe aripothundi kada?
Thank you challa clear ga explain chesaru... SIDs match avali DB restore chesinapude e sp run chesanu and all corresponding DB users match ayayi except 2 which were orphaned so avi malli recreate chesanu ...

I dint restore master DB so may be that is the reason sql level logins and DB level users different unayi..


[b]Okasari confirming if I understood right point mee andarii posts tho[/b] - DB varuke users ki permission isthe saripothundi if they will not login to sql server ... anthe kada
[size=4]Sql level logins kuda kavali ante master DB ni restore cheyali anthe na? [/size]
[/quote]


correct, kaani prathi database user ki oka correspoing server level login kaavali..adi nuv create kooda cheskovachu..ledante you need to transfer the logins, you can use SSIS transfer login task which is easy to do..master db ni restore cheyyadam konchem risky

[deals2buy]

[quote name='Guest' timestamp='1362596965' post='1303375749']


correct, kaani prathi database user ki oka correspoing server level login kaavali..adi nuv create kooda cheskovachu..ledante you need to transfer the logins, you can use SSIS transfer login task which is easy to do..master db ni restore cheyyadam konchem risky
[/quote]
akkada DB tappa no SSIS or SSRS anta vaalla daggara

[Guest]

[quote name='Kaarthikeya' timestamp='1362597035' post='1303375753']
akkada DB tappa no SSIS or SSRS anta vaalla daggara
[/quote]


no SSIS yendi..BIDS install aye gaa untadi..oka package create chesi run cheste sari poddi..

[littlemoon]

[quote name='Guest' timestamp='1362596965' post='1303375749']


correct, kaani prathi database user ki oka correspoing server level login kaavali..adi nuv create kooda cheskovachu..ledante you need to transfer the logins, you can use SSIS transfer login task which is easy to do..master db ni restore cheyyadam konchem risky
[/quote] SSIS ledhu ... and memu old DB server nunchi new DB server ki moving - in this process ma old DB server setup sariga ledhu so logins transfer petadhu ani cheparu ikada Dev's

so again confirming.. sql level lo kuda logins create chesthamu and kavalsina DB's ki permissions isthamu.. then verfiy cheyali if that user is existing in required DB's anthe kada?

if this is what I have to do already DB's lo e users unaru.. Sql level lo create chese apudu 'User already exist for this DB' ani error vasthundi ante DB users ni delete chesi recreate cheyala? [size=4] [/size]

[littlemoon]

[quote name='Kaarthikeya' timestamp='1362597035' post='1303375753']
akkada DB tappa no SSIS or SSRS anta vaalla daggara
[/quote]
[quote name='Guest' timestamp='1362597240' post='1303375777']
no SSIS yendi..BIDS install aye gaa untadi..oka package create chesi run cheste sari poddi..
[/quote] BIDS ledhuu na local sys lo veskunaa... chepthunaa epatinuncho BIDS use chedam anii kani opukovatle...

[Guest]

[quote name='littlemoon' timestamp='1362597437' post='1303375796']
SSIS ledhu ... and memu old DB server nunchi new DB server ki moving - in this process ma old DB server setup sariga ledhu so logins transfer petadhu ani cheparu ikada Dev's

so again confirming.. sql level lo kuda logins create chesthamu and kavalsina DB's ki permissions isthamu.. then verfiy cheyali if that user is existing in required DB's anthe kada?

if this is what I have to do already DB's lo e users unaru.. Sql level lo create chese apudu 'User already exist for this DB' ani error vasthundi ante DB users ni delete chesi recreate cheyala?
[/quote]


User already unte delete cheyalsina avasaram ledu...okasari database role memberships, owned schemas, securables anni correct gaa unnayo ledu chusko (User meeda click cheste anni details vastai )..ledante correct gaa assign cheyali..bottom line..old server lo unna same permissions new server lo kooda apply avvali..ade goal

[littlemoon]

[quote name='Guest' timestamp='1362597884' post='1303375841']


User already unte delete cheyalsina avasaram ledu...okasari database role memberships, owned schemas, securables anni correct gaa unnayo ledu chusko (User meeda click cheste anni details vastai )..ledante correct gaa assign cheyali..bottom line..old server lo unna same permissions new server lo kooda apply avvali..ade goal
[/quote]

ya goal ade but each user ni check chesi cheyali antee long time avthundi.. a paina ichina SP laga emaina unte bavundu

anyways thanks a lot