Finding All SQL Users Permissions And Access On Every Server And DB?
Sep 18, 2007
Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs. Each object and permissions
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?
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
I have a program which will show you users logged into a session. It's designed in MS Access and will also record citrix/terminal users. If you would like a copy of the program, download the attachment or email me at paulk@weccusa.org.
I am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1
Hi, Imagine that I have a select something like this select * from T where T.Name Like '%Maria%' "Maria" would be what the user what to find. But for example in spanish we use Ã, ó, ú, ü... The same happens in french, german... The user want to find Maria and does not care if another user (or himself) has inserted the client as Maria, MarÃa, Marïa or whatever. Of course one solution is putting in the DB only the simple characters (a,e,i,o,u) and look for these, replacing the "strange" chars in the application to the "normal" ones. But if we want to be professionals we would need to insert in the DB the name in their original spelling (the image is the most important!) Any idea, help or reference? Thx in advance and happy new year. David
I know sysprocesses has the information, but I can't seem to find how this is done using the dmv's. sessions doesn have a db context, and requests is only active while activity is occurring.
I am converting a multitude of Access databases from 2.0/95/97 to xp and at the same time moving to a client/server model using SQL Server. This is the first time I have used SQL Server and I am having problems that seem to be related to permissions: I am unable to select, go to, update or delete a record via the application I have built. This may be a flaw in my development skills or a problem with permissions. I cannot find how to apply universal permissions to every column in every table (which is what i want at this point) and any changes that I do make are not reflected in Access. Any ideas are gratefully received.
I have created an adp, and have created stored procedures for all of my forms. In the SQL Server database I have given my users permissions as db_datareader and db_datawriter, however they were unable to open any of my forms because they didn€™t have execute permissions on my stored procedures. I finally had to give them permissions as db_owner so they could use my forms. Do I have to give them db_owner permissions to execute my stored procedures? I know I can go in and assign them execute permissions for my stored procedures, but there has to be a better way. Does anyone know how to give users execute permissions on stored procedures without having to give them db_owner permissions or having to grant execute permissions for each stored procedure individually?
I am still in the development phase of my project and having to assign users or groups with each stored procedure I create could really be a pain. Can someone please help me?
Hi there! I'm developing an application and i need to do the next thing: I need to make a process, but this process just need to be done, when nobody else is modyfing datas in the database.
Is there anyway i could check this? I have read about the sp_who stored procedure but this one return all the activity in all the databases, and i just need to check in one specifically. And there's another issue, we all access with the user dbo to the database...
I restored a copy of a database onto a new server. I created the proper logins on the new server prior to the restore. The users and permissions should all be identical. They appear to be in the system tables, but none of the users or permissions appear in the appropriate screens on Enterprise Manager.
Also, even though the permissions appear to be intact in the syspermissions table, when I connect as a certain user and try to query a table the user should have permissions on, I'm denied access.
Is there an SP that can tell the database, the users with access to the database, and their permissions? Meaning the result would be something like... <database> <username> db_datawriter <database> <username> db_datareader <database> <usernameII> db_owner
I have SQL Server 7 database & I need to create some interface between users & database to let users see what is in the database, but not let them change or destroy data. Do I need to create some other database in-between users & the original database? Users can't program & want access to all data.management doesn't want to create a replication of the database. They want to let users have access through MS Word or Access. What could you advise me?
Ive finally gotten my report server setup to run. I can access the reports via IIS from a remote computer if I login as the server administrator. Ive given certain users permissions to the /Program Files/sql server/Reporting Services/ folder, however, when I try to enter their login info, from the IIS login prompt when i visit http://servername/ReportServer/ I am unable to login. How do I grant users permission to login to report server?
Hi, I want to create a new user for my database and allow them to only select data from the tables... CREATE LOGIN NEHardcoreWITH PASSWORD = 'abc'USE aiaccontentdb;CREATE USER NEHardcore FOR LOGIN NEHardcore What permissions will this give to the new user? Do I need to grant any permissions and/or revoke any? Thanks!
I am trying to create a new user for a SQL Server database and use the credentials in an ASP.NET app.
Problem is dbo permissions are not being applied to the database objects when I set up a new user (Logins -> New Logins) with SQL Server Authentication, set the default database to the database I want.
I then get an error message saying that the user has not been granted access to their default database : DBNAME and therefore will not be able to gain access to their default database.
I'm not sure why this is as I am logged in as sa.
Anyway, when I ignore the error and set-up this new user through the Users section of the database none of the dbo permissions carry over.
did anybody encounter or write a stored procedure or a tool that sets all permissions on all users on a given database? It is a major headache to set all permissions again after some changes with a tool like ErWin and then adding some users.
i need to have a user account with the following requirements:
a. can create/alter/drop tables/views that is created by this user; b. can read/update/delete records from tables created by this user c. can read/add records to tables created by other user d. can create/edit/execute stored procedures
is this possible? how can i do this? how can i allow a user to create new objects or alter objects owned by him at the same time prevent him from dropping objects created by other user? how can i give a user full access to objects that he created and limit his access rights like dropping objects, deleting records or updating records from tables that is not created by him?
can i do these thru enterprise manager or do i have to run some scripts in order for these to happen? if so, can somebody help me with the scripts or links that i can use for references?
thanks a lot! hope you can help me with these... thanks a lot...
I have a user that I have granted the server role "securityadministrator" and a database role "db_securityadmin". When logged inas this user I can create new logins but not run sp_adduser to add thenew login to as this says I don't have permission to do this. I canhowever run sp_revokedbaccess to get rid of a user from the database.
I have now succefully setup SQL Server Express on an Admin PC in my office and can see it and connect to it easily from my PC using my NT logon.
My application is complete and runs a treat for me connecting to the server from my PC.
I have now deployed the application using ClickOnce and it has all gone smoothly, except for other NT users gaining access to the database?
Within MSMSE I have clicked on Security-Logins and can see my NT login details are present and as me I can administrate the server/db.
I have added a few new logins and even tried to match them to my settings but from their computers the application fails to logon to the server/database.
Within the ODBC setup the server is visible for them but they cannot connect?
If I logon to their computer it works for me?
This section is new to me so I am struggling to get it to work, can anyone throw me a bone please?
I am working on SQL 7.0/2000. I have given lot of permissions to the user 'duser1'. The permissions like select,etc..,create... Now I want to give the same permissions(what I have given to 'duser1') to the other user called 'duser2'. Right now I hvn't created any Database Roles or Server Roles.
Do we have any easy method to copy the permissions of one user to the other user?, like script generation or any method. If anybody knows that please guide me.
I have an archival process on a large database that runs once a month.At the beginning of the process the triggers and indexes on thetables whose data is moved are dropped, the data is moved and then thetriggers and indexes are recreated at the end. This produces amassive improvement in performance.The problem is the process is supposed to run on users accounts (thatsthe way the front-end is set up) and they don't have the neccessarypermissions to drop & create triggers & indexes. I can't see any wayto give them permissions only on specific tables or triggers/indexes.Nor does giving them permissions to the stored procedures that do thedropping & re-creating work, DDL permissions don't seem to beinherited the way they are with tables.Is blanket rights to drop & create objects through the db_ddladminrole the only way users can get rights?Thanks,K Finegan
As our customers demand that we tighten our IT security in the company,I've been asked to prepare a report quarterly showing, for each user inActive directory, what his effective permissions are for every table inevery database that he has permission for on our SQL Server 2000 server. Isearched a bit for a tool to do this, but all I found was the PERMISSIONS()function for showing effective permissions of the current user. Is thereany way to do it for an arbitrary user, without logging in as them?
I have looked and looked and looked and can not find an answer to this simple question. I want a way to script out all the permissions for a given user.
I have a user (User1) and he has the following permissions lets say:
Objects - SELECT ON schema3.table1 - EXECUTE on schema4.storedproc1
I need a something (script, report, voodoo magic spell) that will show me this. It is hard to believe that this is not a built in report with SQL2005. It was hard to do in 2000 but you could do it if you played around with the scripting options enough. I can not find a clear cut way of doing this in 2005 to save my life. Maybe I am just blind, but my fellow DBA's have the same problem.
We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.
I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.
The Setup
Client UPS WorldShip 8.0 running on XP Pro SP2 Connecting via Sql Native Client via SQL Server Login Connection is over a T1 via VPN
Server - SQL Server Standard Edition on Windows Server 2003 2x3ghz Xeon processors w/ 4gb ram
The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.
What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following
This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.
I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.
1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag 2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag 3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag 4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag
Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.
Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.
Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)
Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?
Is there any easy way to move users between SQL Servers for a particular database? We have tests and development servers I would like to make sure the user lists are in sync for that db.
I need to be able to set up a table listing users permissions, this needs to be run daily and then notify me of any changes without using the auditing or profiler software.
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.
