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 in
Active directory, what his effective permissions are for every table in
every database that he has permission for on our SQL Server 2000 server. I
searched 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 there
any way to do it for an arbitrary user, without logging in as them?
In SSMSE, if I open Server Properties and then Permissions, I can highlight a user or user group and click the 'Effective Permissions' button.
For entries like 'BUILTINUsers' or 'BUILTINAdminsitrators' I get the following error message. I don't understand what it's telling me - can anyone explain?
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot execute as the server principal because the principal "BUILTINUsers" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15406)
Notes: as fara as I know, BUILTINUsers does exist, and I do have permission.
Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....
My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -
SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.
So I decided to try the simplest possible scenario to make it work:
I've created a login DOMAINewEmployee with Windows authentication.
DOMAINewEmployee has been granted access to databasename.
By default, DOMAINewEmployee is a member of Public.
Public has been granted all available permissions on all objects.
ie... grant all on userTables to public
........grant all on sysobjects to public
........grant all on otherSystemTables to public
etc.
Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAINewEmployee, querying -
select * from dbo.sysobjects
via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).
To compare, I then created a login newEmployee2 with SQL Server authentication.
newEmployee2 has been granted access to databasename.
select * from dbo.sysobjects
runs successfully from Query Analyser (as to any queries on user-created tables).
What else is required to grant access to tables from a Windows authenticated login?
( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status( ID varchar(50), Status varchar(50), EffectiveStartDate datetime, EffectiveEndDate datetime, Is_Current bit
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
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 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
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.
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.
I am in need of a utility (stored procedure, third party app, etc)that will help streamline the process of managing and auditing objectpermissions, users and roles on multiple sql server 2000 instanceswith multiple databases on each instance. That's a quick overview ofwhat I need, do I need to be more specific?Thanks.
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.
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?
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?
We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
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've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users? thanks,
Hi, Is it possible to find out who has accessed a certain report for a given time period? Does Reporting Services store this data somewhere? If so how can I access it?
I created a user on the system with a limited access privilege (Users group member). I need this user to see only specific reports when he login in to the report manager. I gave him a browser role on the report manager and went to the individual report and remove the permission. However, when this user go to the report manager he is not only browsing but also can do whatever the admin can do. I'm just confused. This user is created with a limited access and provided only a browser role, how can he act like an admin?
I€™m trying to use Reporting Services for the first time. I€™ve created a couple of reports and loaded them onto the server through Visual Studio 2005. I got all that to work with very few problems. I can even view the reports by going to: http://servername/reportserver?%2fReportProjectName/ReportName And that lets me run the run the report, print it out, etc. However, when I try to log into the report server by going to http://servername/reports , I get a SQL Server Reporting Error: An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help. I am an administrator on the SQL Server and I have the Content Manager role on report server. The error log gives me 2 warnings: Event ID: 1010 Source SQL Dumper Bucket 51209465, bucket table 5 For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. And Event ID 5001 Source SQL Dumper Bucket 51209465, bucket table 5, EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.3042.0, P6 45cd6edb, P7 0, P8 00005283, P9 00000000, P10 NIL.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. And it also gives me the error: Event ID 5000 Source SQL Dumper EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.3042.0, P6 45cd6edb, P7 0, P8 00005283, P9 00000000, P10 NIL. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. I€™ve Googled all of the errors and warnings and not gotten anywhere. I€™ve been working on this for a week and half now and not getting anywhere. I€™m running SQL Server 2005 SP2 on a Windows Server 2003 box. Thanks for any help you can provide, -JP
