Database User Privilege Report
Jul 23, 2005
hi, can anyone help me out with this report.
I need to produce a report for the database level user permission.
like for a database: DB_A, I want to output as such:
[SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] .... other
public G D
guest D D D D D
dbo G G G G G
user_A G D D D G
G stands for Grant and D stands for Deny.
I need not only to look at direct grant/deny but also whether that user
is a member of fixed database role and fixed server role. like dbo will
have G on all.
I am trying to look into sysprotects table and looking at lines with id
= 0 but I just can't get to know what different action and protecttype
numbers means!
Many thanks
View 1 Replies
ADVERTISEMENT
May 26, 2008
Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
View 1 Replies
View Related
Feb 18, 2014
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies
View Related
Aug 24, 2006
it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?regards,
View 5 Replies
View Related
Feb 16, 1999
Using SQL 6.5 SP4.
TIA.
Zak
View 2 Replies
View Related
Mar 30, 2006
Hi,
My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and
with Domain Admin privilege, my app is running ok.
I asked admin person to upgrade domain user privilege to connect SQL server,
but he couldn't do this for security reason, and told it should work with domain user.
Is it true for domain user to connect SQL server without any problem?
Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?
Or inside users of my database section of SQL server EManager?
Thanks,
dixon
View 1 Replies
View Related
Sep 28, 2005
Hi. Thru a sproc, I drop & re-create some temp tables.When I call that sproc from the client, though, I cannot drop thetables.I need to allow the user, say "Alex", to drop/create tables (actually,that would be DDL). Which role should "Alex" assume ? How do I do that?I run the following sproc named, say, "CREATE_TABLE" (SNIP):__________________________________________________ __________________Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'Exec (@StrSQL)Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] ([GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...........[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]'Exec (@StrSQL)Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+ @TableName + '] TO [Alex]'Exec (@StrSQL)__________________________________________________ __________________As you can see, it is dynamic, because I need to repeat it for many@TableName values - that means, further more, that I will be executingthis in the context of the current user, Alex, and therefore I have togive Alex rights to both executing the sproc and to the tables referredto by the sproc, as specified by @TableName.I created a _TEST sproc which contains only the following:_______________________________________________CREATE PROCEDURE _TEST ASDROP TABLE [dbo].[SomeTable]RETURN_______________________________________________When I execute it from the client, thru ADODB, on user Alex, I get"User does not have permission to execute this operation on tableSomeTable"The table has been created thru "CREATE_TABLE", abovePlease help, I have to finish this tomorrow, and I'm under tons ofpressure.Thanks a lot,Alex.
View 1 Replies
View Related
Jul 22, 2014
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
View 1 Replies
View Related
May 26, 2015
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies
View Related
May 12, 2008
An user member of users,what's the sufficient privileges?
View 2 Replies
View Related
Apr 29, 2015
What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
View 1 Replies
View Related
Dec 18, 2007
We are using Crystal Report Services XI to generate reports from SQL Server 2000 database running on server within the same domain (LAN).
for some reason, SQL Server is rejecting the Credentials that is provided to CR Services, the user has right to that database. When We run the report manual, it works fine, but when we run Automatic on schedule , it fails.
The Authentication mode is set to Mixed.
The Startup Service accoutn is set to "This Account"
Any idea, why would this failure on User is encoutered.
Thanks,
View 1 Replies
View Related
Jan 23, 2007
Hi,
I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!
Regards,
Kenneth Lai
Programmer
Error Pic
Message Box
View 1 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Jan 11, 2008
Hello. I am trying to write a report that pulls information in from Active Directory. I have a view created that gets a listing of users and a view that creates a listing of user groups, but I can't seem to figure out how to get all user groups that are associated with the users. This is what I have.
SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, 'SELECT cn, groupMembershipSAM FROM ''LDAP://wmdomain.local'' WHERE objectcategory=''group''') Rowset_1
SELECT *
FROM OPENQUERY(ADSI,
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn
FROM ''LDAP://wmdomain.local''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Rowset_1
Let me know if you have any suggestions!
BJ
View 1 Replies
View Related
Oct 9, 2015
I have done the following and a domain user would not access report created a login to the SQL server to the user (this SQL Server is where data source DB is)went to site setting in Report Manager and made this use a system userright clicked on report folder and made this user in the browser roleeven checked that in the report in question, the user is already in the browser role Still the user would not access the report! "User .......... does not have required permission" is the error message I am getting.
View 6 Replies
View Related
Feb 21, 2007
Hi,
I have environment that use SSRS 2005 ad hoc reporting.
One of the users (out of 200) is unable to open or save reports in the report builder.
the user succeed to open the report builder, when he try to save report that he build from shema he get error. as well when he try to open an existing report that build in the report builder he also get an error,
On open the error is:
The numeric portion of 2pt cannot be parsed as a unit of the type point.
The Initilizer for 'Microsoft.ReportingServices.Design.Constants' threw an exception
On Save the error is:
System.IO.StreamReader: Deserialization failed: The type initializer for 'Microsoft.ReportingServices.Design.Constants' threw an exception. Line 2, position 2
Please note that I succeed with second user to create ad hoc report on the first user
computer. I also failed to with the first user to create ad hoc on another computer.
It seems like permissions issue
Thanks,
Assaf
View 3 Replies
View Related
Jan 25, 2007
In SQL 2000, how do I grant system administrator privilege to "sa" or a Windows User?
View 3 Replies
View Related
Jun 16, 2006
SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
View 5 Replies
View Related
May 31, 2007
I am recieving error 4606 Granted or revoked privilege SELECT is not compatiable with object.
Canot read the next data row for the dataset BEEP.
However when I use the same userid/pwsd in Crystal to access the same stored proceedure I have no problems accessing the data.
We have already tried re-granting the permissions to the userid to no avail.
Are there any other suggestions?
Phread
View 1 Replies
View Related
May 12, 2006
I'm using our development ID to set up peer-to-peer replication. I am following the steps outlined in the BOL using SQL Studio 2005. When I created the initial publication, I read the section on permissions and assigned the development login ID db_owner. Indeed, to eliminate any possible issues (I thought), I gave it db_datareader, db_datawriter, and all the db admin rights and made very sure the db_denydatareader and db_denydatawriter roles were not checked. I did this through the Logins section of the Security folder and applied the same rights to both the distribution and the publication database. Nevertheless, when I run the LogReader agent job, it fails with the message for step 2 that:
Executed as user: ourdomsqldev. A required privilege is not held by the client. The step failed.
The dev ID essentially has sa rights to the server and all the databases. It is also a domain authenticated ID. Any ideas what's causing this problem? I really need to get the replication going reliably before the weekend is out.
Thanks.
Randy
View 1 Replies
View Related
Aug 20, 2007
I keep getting this error message on the subscription execution, even though the account is a domain administrator. Both publisher and subscriber are on the same domain. Can someone tell me what is the problem?
Message
Replication-Replication Distribution Subsystem: agent [Agent Name] failed. Executed as user: [Domain]administrator. A required privilege is not held by the client. The step failed.
Regards,
View 1 Replies
View Related
Apr 3, 2007
I am getting this error message "Replication-agentclassname: agent F1TESTSQLSERVER20-ELC-ELC_Pub-F1TESTSQLExpress-39 failed. Executed as user: . A required privilege is not held by the client. The step failed." All the users replication agents have failed and I am looking on how to resolve this serious error. Where do I start? I am using merge replication.
Thanks!
SAM
View 2 Replies
View Related
Nov 30, 2007
Hi there,
I want to create database login and set that login to owner of the database? Can anyone help me?
thanks
View 3 Replies
View Related
Mar 3, 2008
Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
View 3 Replies
View Related
Apr 25, 2007
Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I would be grateful if you can help me
View 1 Replies
View Related
Jul 21, 2015
Running a report on sqlserver RS 2008 R2 that uses a data source that looks at a database on a sql express 2012 server.
I can run the report in preview mode from bits on the sql 2008 r2 server.
I have tested the connection of the deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.
I can logon to sql express 2012 using management studio and logon as the user and access the database run stored procedures etc.
windows server 2003, sql server 2008 R2 reporting services server.
windows server 2012 sql express 2012 data source database location.
View 13 Replies
View Related
Jan 15, 2004
Hi,
I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...
We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.
The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.
I have checked for orphaned users and the user I am logging in with from the webpage is not listed.
So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.
Thanks for your help, appreciated.
James.
View 9 Replies
View Related
Feb 27, 2008
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
exec sp_dboption 'database_name', 'single user', ''FALSE'
again by using the Management Express Tool.
Can someone please help, in plain english???? Thanks
View 5 Replies
View Related
Dec 12, 2007
Hello,
We have a new production server with SQLserver 2005.
Our department responsible ofr the installation made it as secure as possible, however.
I have to put a new backup on to the server from the old production server.
We ran into a problem when we removed the old database.
I could not longer logon to the server with an authenticated user.
"A connection was successfully established with the server, but an error occured during the login process"
"Shared memory provider : error 0 : No process is on the other end of the pipe"
Strange thing is when i create a new database with the same name. The user can logon to the server again.
Any idea's ?
Jurgen
View 1 Replies
View Related
Jun 16, 2015
I running SSIS package job without sql agent , it is working fine.when i am running through sql agent not running.
created Proxy account
job failed and give above error.
Server is cluster and taking data from desktop.
server is in one domain and desktop in another domain.
View 3 Replies
View Related
Nov 22, 2007
i'm using the Enterpirse library logger to write logs into a database.
When choosing connection string i choose the database i want in the "connection properties" dialog box and
push 'Test connection' button.
everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things,
from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore....
it only work when i restart the computer.
why ?
View 3 Replies
View Related
Feb 6, 2008
I've read all the posts... set the security policy (for the service account running SQL Server) to lock pages in memory... ran gpupdate... still same error... HELP !!!!
View 1 Replies
View Related
