Userids/logins And Permissions?

Jul 13, 1999

Hello:

We are working on an application but with other users who have been using the SA password. The application is running on MSSQL 6.5 and sp4.
We have been generally assigning logins/userids to new users although some people are using the SA id. The SA id password has been changed so that will stop users (really developers) from using it.

Most users are assigned to a group which has select, insert, update, delete
permission. Some users will be moved to a group which as only select privilege

I would like to query the system tables and see exactly what permissions
are assigned to each user.

I can look at Enterprise manager and cut and paste this information I think.This is cumbersome

1) Does anyone have a query I can run to get the userid/login and the permissions for each userid?

2) How can I make a user a dbo and give him/her the same permissions as a dbo on tablesalready creaetd?

Thanks.

David Spaisman

View 1 Replies


ADVERTISEMENT

History Of Logins Or Userids

Nov 2, 2005

Hello All,Does SQL Sever 2000 keep track of all the logins/userids that weredeleted over the last year? For example, If I deleted a login "Joe"from a SQL 2000 server, (and of course the corresponding userid "Joe"got deleted from the database) would that be recorded somewhere in thesystem or the production database? i.e. the information such as thelogin "joe" deleted on such and such date from such and such database?Long Live SOX :)Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***

View 4 Replies View Related

Most Logins Dropped-No Permissions

Mar 28, 2007

Hi,



In my local instance of SQL Server at work (which I use for testing), somehow all of the logins except 2 were dropped. The administrators group no longer exists as a login (nor do any other windows users or groups)-the only 2 remaining are sa (which is disabled) and a SQL login I had created earlier which has no permissions except read permission on master. I can login as this SQL user, but I do not have permissions to create logins or enable the sa account. Do I have any options other than uninstalling and reinstalling my local instance?



Thanks,



-Dave

View 5 Replies View Related

Adding Logins And Permissions

Jun 9, 2006

Hi,

I am at a company with 18 employees and I have 11-12 databases in SQL
server. I can't seem to give logins and permissions to groups. Is there
a simpler way, or do I have to add every single employee to each
database and give permissions?

View 1 Replies View Related

Migrating SQL Server Logins/permissions

Apr 12, 2000

I need to move several databases to a new server while retaining the same logins/permissions. Books Online indicates that DTS can move the SQL Server logins, but it sets the passwords to NULL in the process. Is there any way to move the logins and keep all passwords/permissions intact?

View 2 Replies View Related

Migrate Logins And User Permissions

Jul 31, 2012

I know that there is Microsoft KB to migrate SQL Logins but it doesnt take care of Login Server level permissions or User level permissions.Idera used to have a Free tool SQLPermisions.exe but it works only on Windows XP/Vista not on Windows 7. Any third party tool (free or paid) which can migrate SQL Logins and User permissions ?

View 14 Replies View Related

Backing Up Logins And Permissions For SQL Servers

May 31, 2006

Hi,I work as an IT Administrator and part of my job role is to useEnterprise Manager and Query Analyzer to backup the logins andpermissions for each database on our SQL Servers. This information isused as a backup in the event of a server failing so we then havesomething to fall back on if we need to add the same logins/permissionsagain.This takes ages to do, as I have to manually enter all the informationinto a spreadsheet.Steps:- In Enterprise Manager I connect to a server then 'Security' tab, then'Logins'. I then enter all items into a spreadsheet.- In Query Analyzer I connect to a server then do 'sp_helpuser' to listall the permissions for each database. I then enter all informationinto a spreadsheet.I was wondering if there is such a program available on the market thatwould do this for me? I know that MS Baseline Security can show flatfile share permissions to a certain degree and was wondering if therewas a similar program out there to help me do my job easier, or ifthere is a simple way of doing this in the future...Any feedback would be greatly appreciated.Regards,Jenny

View 2 Replies View Related

Enumerate SQL Server Logins And Permissions

Sep 10, 2007



Haven't been able to find the answer to this after sever searches. So decided to open the thread myself.

I am trying to write a report enumerating logins in SQL Server and all database-level and object-level permissions granted to each login.

Can someone tell me what system objects I can query to fetch this information?

TIA.

View 3 Replies View Related

Script To Copy Logins And Users With All Permissions

Jan 26, 2007

Hello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.

View 3 Replies View Related

SQL Server Admin 2014 :: Logins With No Database Permissions

Nov 3, 2014

Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.

For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.

View 3 Replies View Related

Permissions To See Server Logins/Create Database Users

Feb 2, 2007

Our company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?



Michelle

View 1 Replies View Related

Way To Determine Which Logins Have Write Permissions To Database On Sever

Nov 15, 2007

Hi there,

Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.

Any help is appreciated. Many thanks!

View 1 Replies View Related

Permissions Hierarchy - GRANT On Logins/users/roles

Jun 20, 2005

A question on the permissions hierarchy:
Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?

View 3 Replies View Related

Migrating Userids

Dec 19, 2000

Has anybody been successful migrating userids from one server to another server using SQL7.0? If so, is anyone willing to please provide any sample scripts.

View 1 Replies View Related

Lost SQL Logins, But Still Have DB Logins - Script To Recreate SQL Logins?

Jan 21, 2007

ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.

Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?

I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.

Any assistance would be greatly appreciated.

Thanks,

John

View 3 Replies View Related

Can't Connect Using Specific UserIds And Passwords

Apr 12, 2006

Folks, I'm brand new to MSSQL, I come from a DB2 background.

I've installed SQL Server Express Edition on my WinXP (SP2) system. I'm able to connect to it from a command prompt using "sqlcmd -S CS288290-ASQLEXPRESS -e".

Now I want to connect using a specific UserId and Password. So I changed my "LoginMode" registry variable to "0x00000002" and restarted the service. But now when I issue "sqlcmd -S CS288290-ASQLEXPRESS -U g2user -P g2pwd", I get error:

Msg 18456, Level 14, State 1, Server CS288290-ASQLEXPRESS, Line 1
Login failed for user 'g2user'.

What am I missing?

Thanks.

View 7 Replies View Related

Export Logins To Server With Already Existing Logins

Jul 17, 2001

Hello,

I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.

What's the best way to solve this problem?
Please, help
Thanks

View 3 Replies View Related

Moving DBs From SQL Server Logins To Windows Logins

Apr 3, 2007

I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.

The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.

Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.

But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)

Hope this all makes sense.

View 2 Replies View Related

Table Permissions Versus View Permissions

Aug 2, 2006

Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.

View 5 Replies View Related

Using Different Logins

Apr 12, 2004

How do you make Asp.net use something other than the local ASPNET user for data access in sql server

View 3 Replies View Related

Logins

Jul 10, 2001

I need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later.
I clearly need to maintain all the security options for the new logins.
Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that?
Thank you.

View 2 Replies View Related

How To Map Logins...

Dec 21, 2000

Hi,

I have two servers (server1, server2). I was trying to access the data from server1 to server2. I linked the servers and set up the option for data access.
when I run the following command from server1: sp_remoteoption 'server2', 'sa', 'sa', TRUSTED, TRUE

It gives me the following error. How to handle this.

Server: Msg 15185, Level 16, State 1, Line 0
There is no remote user 'sa' mapped to local user 'sa' from the remote server 'server2'.

I appreciate your help.

Thank you.

Eric s.

View 1 Replies View Related

700 Logins

Apr 13, 2000

I have a server that was recently upgraded from SQL 6.5 to 7.0 that contains almost 700 logins using standard security. Is there a limit to the number of logins that SQL will host? The logins/users have all been added using a GUI within an application and does not support NT authentication. Does this cause any known problems in SQL 7? When I right click on the database and go to properties / permissions MMC gets hung up. Also one of the systems analysts is convinced that SQL is dropping permissions (I disagree to this). Any one have any thoughts or experience with large numbers of logins / users?

Linda

View 3 Replies View Related

Logins

Jun 19, 2000

Does anyone know of any 3rd party software applications that will make users change their SQL
logins every 3 months?
Thanks,
Heather

View 1 Replies View Related

Logins

Mar 14, 2000

Hi!
How can I copy all logins from one SQL server 6.5 to
another one.
Thank you.
Anny.

View 1 Replies View Related

Logins

Sep 30, 2004

How can I determine which users are using the database??

Thanks

View 2 Replies View Related

About Logins

Feb 28, 2008

I am new in sql and have problems about logins.

with sql 2005
1)I create a windows login in my home pc (win xp home sp2) and i can connect server with ssms this new user

but in my office (win xp pro sp2) create a windows login (it is a windows user also) and when try to connect with ssms take error 18456 (user name and pass is true!!!)

2)in my office pc i create a sql login which has no role. but this user can create users and dbs. but in my home pc, user has no role cant do thats...:eek:

View 1 Replies View Related

Sql Logins

May 15, 2008

Hi everyone,I am new to this forum.I hope some body will help me in this,I am trying this for the past 6hrs.
on my server there are some hundreds of sql logins are there,I need to identify the logins which doesn't associated with either any databases or dbroles,server roles.Later i need to delete those logins.Can any body help me in this.

Thanks in advance.

View 13 Replies View Related

Logins

May 22, 2008

y r u doing like dis guys?

View 4 Replies View Related

Logins

Jun 12, 2008

Hi All,

I want to generate script for users and their associated permissions on the xyz database and at the same time I want to generate script for associated logins and their roles over the xyz database.

I am using SQL 2005 environment.

Needs urgent help.

Regards,
Austin

View 2 Replies View Related

Logins

Apr 19, 2007

2 issues
i login using windows authentication. looks like i cant create a new sql server authentication login. what can i do?

and when i try to login with my sa login it pops the errror 18452. does this mean i didnt install mixed mode? i cant remeber.

View 6 Replies View Related

Logins What They Are?

Jun 13, 2007

After installing 2005 I have these 5 logins. Do I need them or should i wipe them out? What are they?

YPGA-SQL2005-DEVSQLServer2005MSFTEUser$IPG-SQL2005-DEV$MSSQLSERVER
YPGA-SQL2005-DEVSQLServer2005MSSQLUser$IPG-SQL2005-DEV$MSSQLSERVER
YPGA-SQL2005-DEVSQLServer2005SQLAgentUser$IPG-SQL2005-DEV$MSSQLSERVER
BUILTINAdministrators
NT AUTHORITYSYSTEM

=============================
http://www.sqlserverstudy.com

View 2 Replies View Related

NT Logins

Aug 7, 2007

We created web site which has NT Authentication....

Using service account
SERVICE ACCOUNT
WEBGROUP
This has WEBGROUP_USERS

WEBGROUP_USERS
Tracey
John

When myself logs in...
Connect To SQL i see

in sysprocesses WEBGROUP

Is there a way to pass TRACEY to the process rather than having
SQL Login set up..

Reason is if someone adds themselves to this group in AD they could have access to our applications im trying to figure out
how to not make AD drive the access to SQL.

Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved