InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:21:20 GMT20http://www.sqlservercentral.com/Forums/Topic1303627-359-1.aspxI have created a database role that denies all but select on an object. I assigned this role to a user and the user is getting select permission denied when it is explicitely granted in the database role. I removed the user from the role and they are able to select but when added to the role the select permission is denied. Again SELECT is the only GRANTED permission to this table in the role. Any idea what could be happening here?Mon, 21 May 2012 12:56:31 GMTelizabeth.murray 24129http://www.sqlservercentral.com/Forums/Topic1301675-359-1.aspxHi All,We use sql server 2005 and 2008. I need to trace INSERTED,DELETED and UPDATED records in some tables in this manner. - LogID (auto increment) - TableName - LogType (Insert,Delete,Update) - LogDesc (Inserted Cust_ID = 001, Cust_Name = John etc....) (Deleted Cust_ID = 002) (Updated Cust_Name From John to David) - LogDateTime - LogUserI've searched but no luck, samples only shows duplicate table of the audited table. But i only need ONE table for all logsPlease share your ideas. Thanks.Thu, 17 May 2012 06:05:34 GMTrjs123431http://www.sqlservercentral.com/Forums/Topic527155-359-1.aspxHi All,Got a SQL Server 2005 problem whereby we get the following error: ExecuteDTS (X import):[Microsoft] [ODBC SQL Server Driver] [SQL Server] An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code:'1326'This used to work but the password was changed last week for the SQL Server service account. We've checked everything and even rekeyed the password. I've google supported it but couldn't find a satisfactory answer.Any ideas ?many thanks,John P.Wed, 02 Jul 2008 04:53:14 GMTJohn Parker-202766http://www.sqlservercentral.com/Forums/Topic1302324-359-1.aspxHello, I have critical tables in mssql 2005 I would like to audit the various actions of adding, editing and deletion. If possible get the results in the form of logs.I count on your answers expensive DBA.Sincerely;Fri, 18 May 2012 02:12:28 GMTjabinmoundossahttp://www.sqlservercentral.com/Forums/Topic1301104-359-1.aspxHi,I have one login lowman, it is not getting to login to sqlserver. Can any one say what is the prob....In which place it is having prob....Wed, 16 May 2012 08:52:31 GMTmohan.bndrhttp://www.sqlservercentral.com/Forums/Topic1295887-359-1.aspxI've become more involved in patching our servers and workstations. I'm curious about how other companies approach this. There always seems to be a big concern about security until the point that patching breaks something. Then management often wants to back off on patching rather than fix the locally written software.One company I know sets up new sql servers behind multiple firewalls, then never patches the OS or sql again -- at least that's what I've heard.Mon, 07 May 2012 06:12:16 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic1295791-359-1.aspxHi guys i install sql server 2005 standard edition sp1 original and after i install it and all services concerned about it successfully. i found problem this problem when i open Database-Security-login-username-righclick on user exist properties-usermapping it hangs when i click user mapping and it found more time may be half hour why this problem made and how i solve this problem.please help methanksMon, 07 May 2012 00:23:38 GMTahmed_elbarbary.2010http://www.sqlservercentral.com/Forums/Topic1271312-359-1.aspxHi All. I keep seeing the following line in all Security Best Practices documnets:•Disable COM components once all COM components have been converted to SQL CLR.How can I disable COM Components?How can I know COM Components have been converted to SQLCLR.I am thinking this does not mean I should disable CLR itself.Please help.Thu, 22 Mar 2012 17:28:39 GMTkennethigirihttp://www.sqlservercentral.com/Forums/Topic1295495-359-1.aspxHello,I have searched for an answer, but have not found exactly what I need. I've created a new database that currently has no views built. Since the developer will be creating views[b] later[/b], I want to GRANT SELECT to the appuser [b]now[/b] so that the appuser can SELECT the views immediately after they are built without me having to grant SELECT to the views as they are built. I also do not want the appuser to SELECT from the tables. How do I accomplish this?This grants SELECT to all tables and views, does it not?[code="other"]use [database]GOGRANT SELECT TO [appuser]GO[/code]Fri, 04 May 2012 16:00:41 GMTreefsharkhttp://www.sqlservercentral.com/Forums/Topic1295000-359-1.aspxHello-For all SQL Authenticated accounts, our corporate policy is to set the "Enfore password policy" setting on. I understand that this allows the OS level password policy to be enacted. I have an issue whereby for SQL Server authenticated accounts, the minimum password length is greater than the standard minimum for OS logins (e.g., Domain accounts need password length to be 8 characters while SQL accounts need to be 16). I'd like to be able run a query that would return the names of the SQL Logins that would have a password that doesn't meet the 16 character limit. Any advise?Regards,Gary HampsonThu, 03 May 2012 18:02:59 GMTGary_Hampsonhttp://www.sqlservercentral.com/Forums/Topic1294455-359-1.aspxdear,which server role assign a login name ,so it may perform backup operations for all databases & as well as Resotre it .Thu, 03 May 2012 04:10:51 GMTmca.vimalhttp://www.sqlservercentral.com/Forums/Topic1282221-359-1.aspxwhen right clickin one sql server login (sql sqlauthentication ) properties when clicking on securable option it is commming value doesnot fall within the range (.sqlmgmt) how to solve thisThu, 12 Apr 2012 04:09:05 GMTnaga.rohitkumarhttp://www.sqlservercentral.com/Forums/Topic497615-359-1.aspxI have the following stored procedure to create a login on SQL Server 2005. As is it works perfectly but I want to pass a parameter for the login name but keeps getting incorrect syntax errors. The script looks as follows:CREATE PROCEDURE dbo.Create_LoginASBEGIN SET NOCOUNT ONCREATE LOGIN [johnny] WITH PASSWORD = '12345', DEFAULT_DATABASE=[dbname], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ONEXEC sys.sp_addsrvrolemember @loginame = N'johnny', @rolename = N'sysadmin'ALTER LOGIN [@username] DISABLEENDWhen I pass the parameter it looks like this:CREATE PROCEDURE dbo.Create_Login ( @username varchar(50) )ASBEGIN SET NOCOUNT ONCREATE LOGIN @username WITH PASSWORD = '12345', DEFAULT_DATABASE=[dbname], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ONEXEC sys.sp_addsrvrolemember @loginame = @username, @rolename = N'sysadmin'ALTER LOGIN @username DISABLEENDPlease can someone help me to get this right. I have searched but nowhere any site says anything about passing a parameterThanksManie VersterFri, 09 May 2008 00:52:30 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic1283372-359-1.aspxI've got a batch of about 1000 stored procedures I'm granting a user permission to execute. It's just a group of 1000 GRANT EXECUTE ON [spName] TO [WindowsLogin] statements.I have two copies of the same database on a server (Identical SPs and tables across the database, different data in the tables, both about 4-5GB of data), and for one of the databases the script runs in 10 seconds, for the other it takes just over two minutes. Both databases have their data and log files on the same disk, and while running the scripts I'm seeing no evidence of poor disk performance, high CPU or RAM usage. Any suggestions as to what could possibly making this script to run longer on one database than the other? It's not really a huge deal since they are low-use databases and everything else (Inserts, Updates, SP executions, etc.) is running fine performance-wise.Thanks,JasonFri, 13 Apr 2012 13:01:47 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic671366-359-1.aspxWe have a request from client to hide all system views/tables from users in SQL server 2005.As user assigned to a specific database role, client do not want the user to see all system tables and INFORMATION_SCHEMA views, so they can have a clear view for only user tables in their schema.However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views.Also when connecting from SQL Management Studio, they are getting same list.We have taken following steps, but no luck.1. DENY permissions on "View Definition" at all scope levels but still the users can see all these views using ODBC.2. Tried denying access by changing permissions to deny in the public role, but still the same.3. Created one Role including deny permissions to all sys and INFORMATION_SCHEMA views and assigned to user, but same issue.Please advise is there any way of doing itMon, 09 Mar 2009 05:49:04 GMTsivaprasadhttp://www.sqlservercentral.com/Forums/Topic1280932-359-1.aspxIs there are way to give a user access to edit a particular SQL Job?Tue, 10 Apr 2012 10:29:56 GMTRemRodhttp://www.sqlservercentral.com/Forums/Topic1278912-359-1.aspxSyslogin table stores login name and password.Is there any history table which saves the password a user had past three times.Unfortunately my Java Application uses sql login for each users and I have to implement password policy.Since we do not store userid and password in a table and we try to connect for each user using the sql login and password.I update the syslogin table when ever user changes the password.Now ,I want user should not enter any password he used last three times.If there is a history maintened somewhere about sql logins in syslogins table ,I will be able to do that.Please help.Thanks,Thu, 05 Apr 2012 09:13:44 GMTPink123http://www.sqlservercentral.com/Forums/Topic1276557-359-1.aspxHi,I've put together a stored procedure to check a few vital SQL Server functions. One thing we'd like to check is whether the kerberos double-hop is working.My set-up:I have 2 servers; server1 and server2On server1, there's a linked server called 'CAD' pointing to server2On server2, there's a linked server called 'REP' pointing to server1In code, to check whether kerberos double-hop works between these two servers, I run the following from server1[code="sql"]EXEC('SELECT TOP 1 * FROM REP.mytestdb.sys.databases') AT [CAD][/code]However, this always seems to generate a 'Login failed for user 'NT Authority\Anonymous Logon' message. However, if I log on to a 3rd server, i.e. server3, connect to server1 and run:[code="sql"]SELECT TOP 1 * FROM CAD.mytestdb.sys.databases[/code]I get results back. However, in my solution, I can only use server1 and server2 to perform the check.What am I doing wrong here?Is there a better way to simulate/check a double hop.Thanks, AndrewMon, 02 Apr 2012 05:24:04 GMTadb2303http://www.sqlservercentral.com/Forums/Topic751807-359-1.aspxHi, I'm trying to set up access for a group of people at my organisation to be able to have SELECT access to data within a database.I don't want them to have access to all the columns in certain tables, so have created a series of views (I call them 'z-views', e.g. zName covers a table called Name) that they can then use to query instead of the tables, and return the data they require.The dbuser belongs to custom-created schema, 'Reports'. The user is also a member of a db role, 'Reporting', to which the various permissions have been granted, or denied.I don't mind if they create their own views to help with their extracts, nor do I mind if they create their own tables. Because the user belongs to the schema 'Reports', their objects should be separate to the objects that I'm trying to protect, which all belong to 'dbo'.What I have so far:[code=”sql”]CREATE SCHEMA Reports CREATE USER [Reports] FOR LOGIN [Reports] WITH DEFAULT_SCHEMA = ReportsCREATE ROLE Reporting AUTHORIZATION db_securityadminexec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'GRANT CREATE VIEW TO ReportingGRANT CREATE TABLE TO ReportingGRANT ALTER ON SCHEMA:: Reports TO ReportingGRANT SELECT ON SCHEMA:: Reports TO ReportingDENY ALL, VIEW DEFINITION ON [dbo].[Name] TO Reporting;DENY ALL ON [dbo].[zName] TO Reporting;GRANT SELECT, VIEW DEFINITION ON [dbo].[zName] TO Reporting; -- I want them to be able to select and view the definition of the view, but nothing else.[/code]Logging in as 'Reports', I am unable to see, or select from, the table 'Name'. I can, however, see the definition of and select from zName. I cannot alter the view in any way.However, I CAN create a view called 'vName' that appears as 'Reports.vName' that selects all the columns from the Name table:[code=”sql”]if exists (select name from sys.views where name = 'vName') drop view vNamegocreate view vNameas select top 10 *from dbo.namegoselect quotename(schema_name(schema_id), '[') + '.' + quotename(name, '[') as ObjectNamefrom sys.objectswhere name = 'vName'select * from vname[/code]What permission do I need to deny to the users to stop them from being able to get round my security blocks?Thanks for any help; it's probably something very simple!!!! :)SimonMon, 13 Jul 2009 00:11:36 GMTSimon Baileyhttp://www.sqlservercentral.com/Forums/Topic1276657-359-1.aspxHello all,I apologize if this has been discussed before. In regards to the security "hardening" of SQL server 2005/2008, is there a checklist i.e with scripts on removal of roles , privileges on xp_* that I can refer to in order to properly secure the server as a whole and each database.thanks !Mon, 02 Apr 2012 08:38:53 GMTjohnnyrmtlhttp://www.sqlservercentral.com/Forums/Topic1274617-359-1.aspxHello All,I have a question regarding security. Right now in our company we just have sa account and everyone is using the same sa account to access sql server.We just dont want to happen this anymore.We decided to apply some security to our server.This is my first project as DBA.I have decided to create 2 user groups and assign members for each group.Group 1 (IT group)members should have individual logins and passwords(sql server authentication) and they will be having full access to the server and database.Group 2 (non IT) members should also have individual logins and passwords and they will just have read access to the database.As I cant get rid of sa account I am just planning to change the sa password and that I am going to share only with administrators so that no one can use sa to access the server.Is this a good approach to secure sql server?...Please correct me if I am wrong...Any suggestions or feedback would greatly helpThanksWed, 28 Mar 2012 13:45:48 GMTsrik.kottehttp://www.sqlservercentral.com/Forums/Topic1273171-359-1.aspxHiI have installed sql server 2005 with windows authentication. But later while trying to login, it is throwing "login failed for user" 18452 error. I am a domain user.Hence I have tried to go for mixed mode but since there is no password set, it is also throwing the same error.I am a domain user but not logged in as an administrator though I have the full power of admin.Now I am not able to login as windows or mixed authentication.Please help.Regards,ArupMon, 26 Mar 2012 22:39:19 GMTarup chakrabortyhttp://www.sqlservercentral.com/Forums/Topic1269135-359-1.aspxI am trying to prepare a Win2K 2003 server edition as Reporting Server for 2005 SQL Server.http://localhost/Reports/throws this error:"The report server configuration file specifies localhost for the Web server name. The report server requires that the computer name be specified instead."Some sites suggested changing the RSWebApplication.config to remove localhost.and then I get this error."The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version."The Report Server seems to be behaving okay although shows only this bit on the page: http://localhost/ReportServerlocalhost/ReportServer - /--------------------------------------------------------------------------------Microsoft SQL Server Reporting Services Version 9.00.1406.00 The current RSWebApplication.config file contains this:<Configuration> <UI> <ReportServerUrl>http://localhost/reportserver</ReportServerUrl> <ReportServerVirtualDirectory></ReportServerVirtualDirectory> <ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel> </UI>I have tried many things so far and have checked google thousand times so far for any possible one solution that can work.Can someone guide me please to get this thing working?Thanks,BijMon, 19 Mar 2012 11:37:08 GMTkotharibijhttp://www.sqlservercentral.com/Forums/Topic1270532-359-1.aspxHi All,I was unable to find this scenario anywhere so I"m looking for some advice. We are moving from a Windows 2003 domain to 2008 but the name has to change as it has an underscore in it.The network team has plans to move the users first (building by building, we have 5) and once all the users have been moved we will move the servers. This will happen over the course of a couple months.There is a trust between the domains but how do I setup Windows Authenticated user accounts in SQL 2005 for users that have been moved to the new domain? The new domain does not come up as a location when I try to create the new security accounts.Their AD accounts will persist on the old domain until the servers are moved, will that be enough to allow them to connect to SQL using windows auth while logged into the new domain? If not how do I get SQL to see the new domain?ThanksKelseyWed, 21 Mar 2012 15:27:03 GMTkvanderkleyhttp://www.sqlservercentral.com/Forums/Topic1270957-359-1.aspxI have a Log Shipping database that is coming from a vendor. We are wanting to let users access the Log Shipping database to create custom reports. The problem that I'm having is that can not add a new user to the Standby/Read-only database. Does anyone have a way to do this?Thu, 22 Mar 2012 09:18:01 GMTRobin Druryhttp://www.sqlservercentral.com/Forums/Topic1270403-359-1.aspxWe have an application that uses windows groups for security in SQL 2008. The windows groups are assigned the roles of datareader and datawriter. The application controls what data users can update/delete. Is it possible for the users in the windows groups to connect to the SQL tables via ODBC and update/delete data - i.e. bypass the application to update to the data? Thanks.Wed, 21 Mar 2012 11:58:23 GMTWestehttp://www.sqlservercentral.com/Forums/Topic518782-359-1.aspxI am unable to unlock user account in sql server 2005.I searched in google and many forums suggest to use Alter login with password='Password' unlock in Management studio.But the problem is I could not even login to the management studio express and the window was closed once the login failed.Please help to resolve this.Wed, 18 Jun 2008 00:58:18 GMTradhashttp://www.sqlservercentral.com/Forums/Topic1268434-359-1.aspxHello All,I have 4 different schema on one database. Each schema is owned by an individual user within our team. No user can access tables within another schema. I am the sysadmin and have all rights on the server.Now, I am trying to grant select permission to one particular table within one schema to the user who owns another schema. I am executing below sql and it runs successfully. It also add the permission on this tables permisison list but the user can not access the table.GRANT SELECT ON [domain\67890].[table1] to [domain\12345]GRANT SELECT ON OBJECT::[domain\67890].[table1] TO [domain\12345]I also tried going to table propertie --> permisisons -- > add users and grant select but it still gives the same error while select.Msg 229, Level 14, State 5, Line 1The SELECT permission was denied on the object 'test', database 'TestDB', schema 'domain\12345'. Any suggestions... Thanks......Fri, 16 Mar 2012 12:06:43 GMTapathttp://www.sqlservercentral.com/Forums/Topic745531-359-1.aspxFor auditing purposes, we need to produce a monthly report showing any logins on our production database, along with permissions to any objects they have been granted.We've used sp_helplogins to produce this information, but we wondered if there is a more comprehensive system stored procedure for this purpose, or if anyone has a custom script they use for retrieving login information?Thanks,KayWed, 01 Jul 2009 09:34:29 GMTkwilthttp://www.sqlservercentral.com/Forums/Topic1267107-359-1.aspxHiI have a sql User password that stopped working with no trace of what happened. Suddenly I could not log in to SQL using that user with the following error:Msg 18456, level 14, State 1, Line 1Login Failed for user ''I checked the syslogins table and this user has never been changed or updated since 2009 (password was never touched), NO policies specified, the user was not locked as this is a SQL user. I restarted the SQL Server service and still I can't login so I had to reenter the password and it worked.I'm really concerned about this because I have no clue of what could have caused this situation.I need your helpAPA0876Wed, 14 Mar 2012 15:14:32 GMTAPA0876http://www.sqlservercentral.com/Forums/Topic1266675-359-1.aspxI am unable to start SQL Server services the error is "couldn't start timely fashion".The service account is local system account. And startup parameters are located to default Data folder.When I'm starting the services from configuration manager errorlog is not updated with new time stamp. Even I tried with Net stop <Instance name> and Net start <Instance name> still same issue persists. And also verified the security and permission to DATA folder no use.Even changed the start up parameters to new location to master.mdf,ldf along with error log.. all my trails no use.Can any one suggest me how to resolve this issue?Wed, 14 Mar 2012 06:59:25 GMTnarayanaswamyhttp://www.sqlservercentral.com/Forums/Topic1265817-359-1.aspxHi All,So many known issues are listed with upgrading to SQL Server 2005 SP3 in the release notes. Issues usch as inability to role back, NTFS Junction Points limitation etc. I would lke to know whether theses problems are relevant when upgrading from SP2 directly to SP4.KennethTue, 13 Mar 2012 05:17:14 GMTkennethigirihttp://www.sqlservercentral.com/Forums/Topic1265642-359-1.aspxRemote users need to run a dotNet app on their home PC. The PC is not on the company domain. The app uses trusted connection to MSSQL. An earlier [url=http://www.sqlservercentral.com/Forums/Topic508113-9-1.aspx#bm508118]post[/url] said this was possible using keymgr.dll to cache credentials. Alternatively, have a network drive mapped to a share on the SQL server, and those creds would be used. Neither method works for me.Is this possible?Mon, 12 Mar 2012 17:13:53 GMTKaborkahttp://www.sqlservercentral.com/Forums/Topic1263373-359-1.aspxHi All,Need small help in writing 2 TSQL queries. SQL Server version SQL 2005 ,sp3.Assume i have a domain account 'domain\user'. This user can be a member of many groups which are explicitly as sqllogins.Query 1 =======I need to know to what all sql groups this 'domain\user' account is part of.Query 2 ======Anyone of the groups can have DENY CONNECT permissions and due to which i am not able to get connected to sql and getting below error in sql errorlog 2012-03-07 10:04:22.19 Logon Error: 18456, Severity: 14, State: 11.2012-03-07 10:04:22.19 Logon Login failed for user 'domain\username'. [CLIENT: <local machine>]and state 11 is server access failure. I want to figure from where the login is getting a DENY permission.One more thing, I tried using xp_logininfo 'domain\username' as sql login who is a sysadmin at sql level. But i dont see any o/p. Ideally, it should show me the permission path and all. Strange thing i dont get any rows as output. Any reasons for that. But again, typing xp_loginfo for some many groups(50) is becoming difficult. That's why is there any dynamic sql queries so that i can acheive what looking for. query1 and qiery2 Thanks in Advance. Wed, 07 Mar 2012 18:22:30 GMTOracle_91http://www.sqlservercentral.com/Forums/Topic951246-359-1.aspxHi allIn 2005 when you installed using domain accounts for SQL Server Service and Agent service, the groups where created in the local users and groups with those accounts, and the groups added to SQL Logins and permissions managed there correct?In 2008 the groups are created in local users and groups, however the domain accounts are not in those groups, nor are the groups or accounts added to SQL logins. Instead I have a NT Service\MSSQLSERVER and NT Service\SQLSERVERAGENT added to the local groups, and also those accounts added directly to SQL logins as Sysadmins.My question is why is my instance working when the domain accounts for the services are not present anywhere in SQL Server, and also what are the NT Service accounts for? Should I add the domain accounts to the correct groups and remove the NT accounts?Tue, 13 Jul 2010 03:36:35 GMTKwisatz78http://www.sqlservercentral.com/Forums/Topic1261836-359-1.aspxI am trying to create a minimum privilege (read only) role which can select from a few tables, through ODBC for an application. When I do this though and test the ODBC using EXCEL, I can see several sys views and also dm views and master db objects.How can I tie all of these down without individually 'revoking' the select access to these on the role?Can I create a role which by default will do this? See only those tables that I allow it to, and NOTHING else.Thanks in advance.sk Mon, 05 Mar 2012 14:29:11 GMTsivay2k1http://www.sqlservercentral.com/Forums/Topic1261844-359-1.aspxI donot seem to understand the schema concept in SQL SERVER.My understanding of a schema is that I could potentially group a few db objects (tables, views & sps) from [dbo] and name them as something 'UserSchema' and set up roles/users to view only these. But I am not able to do so in SQL SERVER 2005. Do I have to release those db objects from the ownership of [dbo] before I assign a new schema owner?Mon, 05 Mar 2012 14:36:07 GMTsivay2k1http://www.sqlservercentral.com/Forums/Topic1260182-359-1.aspxHi all,I've got an interesting problem, and I'm not sure how to fix it. I need to deny select's on a specific table to everyone that connects to the DB. But I can't get it to stick. I have an application that connects via Integrated domain login, so I can set these permissions and then test and it should not allow this application to connect in it's current state. However, I can't get the DENY permissions to work.I used the following to deny everything to all user logins in my domain:[code="sql"]DECLARE @SQL AS NVARCHAR(MAX)SET @SQL = ''SELECT @SQL = @SQL + CASE WHEN @SQL = N'' THEN N'' ELSE N'' END + 'DENY SELECT ON dbo.[Table] TO [' + [name] + '];'FROM sys.server_principalsWHERE [name] like SUBSTRING(SYSTEM_USER, 0, CHARINDEX('\',SYSTEM_USER)+1) + '%'PRINT @SQLEXEC (@SQL)[/code]Which executes the following dynamic sql:[code="sql"]DENY SELECT ON dbo.[Table] TO [ACME\Domain Users];[/code]to deny SELECT for the domain groups. I then checked that this was denied using a script from Lowell[code="sql"]SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';' AS '--Object Level Permissions'FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_idORDER BY perm.permission_name ASC, perm.state_desc ASC[/code]Which returned[code="sql]DENY SELECT ON [dbo].[Table] TO [Domain Users];DENY SELECT ON [dbo].[Table] TO [ACME\Domain Users];[/code]I then checked my credentials (I'm in the ACME\Domain Users group).[code="sql"]EXEC master..xp_logininfo @acctname = 'ACME\venoym',@option = 'all'[/code]It shows the following:[code="plain"]Account Name |type |privilege |mapped login name |permission pathACME\venoym user admin ACME\venoym ACME\Domain Users[/code]According to that, I should be barred from selecting from dbo.[Table]... but I'm still selecting that (in SSMS and in SQL Profiler)Any ideas?Thu, 01 Mar 2012 08:15:33 GMTvenoymhttp://www.sqlservercentral.com/Forums/Topic1260611-359-1.aspxHo do I unlock a user in Sql Server 6.5? Alter login does not work here.Its urgent. pls helpThu, 01 Mar 2012 22:16:13 GMTsare.mschttp://www.sqlservercentral.com/Forums/Topic1254521-359-1.aspxHi ! I create application role and grant all permission in database for it . but , when I want create new login and user in my application, I cannot, although i can select, update in my database. Notes : i tryto use create login or sp_addlogin, but all cannot. Help me, how to grant permission create user and login to my application role . Thanks before.Sun, 19 Feb 2012 21:40:27 GMTducvancong